JDBC Statement Examples – Insert, Delete, Update, Select Record

This article will show you how to use java.sql.Statement to execute insert, update, delete and select sql dml commands with examples.

1. Use java.sql.Statement.execute(String sql) to run insert, update and delete Command.

	
	/* This method can be used to execute insert, update, delete dml command. */
	public void executeSql(String ip, int port, String dbName, String userName, String password, String sql)
	{
		/* Declare the connection and statement object. */
		Connection conn = null;
		Statement stmt = null;
		try
		{
			/* Get connection object. */
			conn = this.getMySqlConnection(ip, port, dbName, userName, password);
			
			/* Get statement object. */
			stmt = conn.createStatement();
			
			/* The method can execute insert, update and delete dml command. */
			stmt.execute(sql);
			
			System.out.println("Execute sql successfuly, " + sql);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			this.closeDBResource(stmt, conn);
		}
	}
     
        /* Close statement and connection after use, this can avoid resource waste. */
	public void closeDBResource(Statement stmt, Connection conn)
	{
		try
		{
			if(stmt!=null)
			{
				stmt.close();
				stmt = null;
			}
			
			if(conn!=null)
			{
				conn.close();
				conn = null;
			}
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

2. Insert and return auto generated keys.

For insert command, use java.sql.Statement.execute(String sql, int autoGeneratedKeys) to insert and get the auto increment keys value, it is the id value in this example.

	/* Execute insert command and return the auto generated record id. */
	public int executeInsertSql(String ip, int port, String dbName, String userName, String password, String sql)
	{
		int ret = -1;
		/* Declare the connection and statement object. */
		Connection conn = null;
		Statement stmt = null;
		try
		{
			/* Get connection object. */
			conn = this.getMySqlConnection(ip, port, dbName, userName, password);
			
			/* Get statement object. */
			stmt = conn.createStatement();
			
			/* The method can execute insert dml command and return auto generated key values. */
			stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = stmt.getGeneratedKeys();
				
			if(rs.next())
			{
				/* Please note the index start from 1 not 0. */
				ret = rs.getInt(1);
			}			
			
			System.out.println("Execute sql successfuly, " + sql);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			this.closeDBResource(stmt, conn);
			return ret;
		}
		
	}

3. Execute select sql command.

	/* This method can be used to execute select dml command. */
	public List executeSelectSql(String ip, int port, String dbName, String userName, String password, String selectSql)
	{
		List ret = new ArrayList();
		/* Declare the connection and statement object. */
		Connection conn = null;
		Statement stmt = null;
		try
		{
			/* Get connection object. */
			conn = this.getMySqlConnection(ip, port, dbName, userName, password);
			
			/* Get statement object. */
			stmt = conn.createStatement();
			
			/* The method can execute select dml command. */
			ResultSet rs = stmt.executeQuery(selectSql);
			
			if(rs!=null)
			{
				while(rs.next())
				{
					int teacherId = rs.getInt("id");
					
					String teacherName = rs.getString("name");
					
					String teahcerEmail = rs.getString("email");
				
					TeacherDTO teacherDto = new TeacherDTO();
					
					teacherDto.setId(teacherId);
					
					teacherDto.setName(teacherName);
					
					teacherDto.setEmail(teahcerEmail);
					
					ret.add(teacherDto);
					
					System.out.println("id = " + teacherDto.getId());
					System.out.println("name = " + teacherDto.getName());
					System.out.println("email = " + teacherDto.getEmail());
					System.out.println("**************************************");
				}
			}
			
			System.out.println("Execute sql successfuly, " + selectSql);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			this.closeDBResource(stmt, conn);
			return ret;
		}
		
	}

TeacherDTO.java

This class is used to save one record data in teacher table.

package com.dev2qa.java.jdbc;

/* This class represent one record in database teacher table. */
public class TeacherDTO {
	
	private int id;
	
	private String name;
	
	private String email;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
}

4. Complete example code.

This example will use a mysql database test and table teacher, teacher table has three columns, they are id, name and email. You can refer JDBC Create Table Example Use Statement to learn more.

READ :   JDBC CallableStatement Stored Procedure Input Parameters Example

teacher table structure

You need to add a column name is id, type is int and check the A_I checkbox to make it auto increment.

set id column auto increment

Example code steps:

  1. Insert one record (hello, [email protected]).
  2. Insert another record (hello1, [email protected]) and return the auto generated record id.
  3. Update name to jerry use the second record id.
  4. Query all records in teacher table.
  5. Delete record which email is [email protected]
  6. List all records in teacher table again.
	public static void main(String[] args) {
		
		/* Below are db connection required data. */
		String ip = "localhost";
		int port = 3306;
		String dbName = "test";
		String userName = "root";
		String password = "";
		
		/* Create an instance. */
		JDBCStatementExample jdbcStatementExample = new JDBCStatementExample();
		
		/* Insert one record. */
		String insertSql = "insert into teacher(name, email) values('hello','[email protected]')";
		/* Execute the insert command. */
		jdbcStatementExample.executeSql(ip, port, dbName, userName, password, insertSql);
		
		/* Insert another record. */
		insertSql = "insert into teacher(name, email) values('hello1','[email protected]')";
		/* Execute the insert command. */
		int autoGenId = jdbcStatementExample.executeInsertSql(ip, port, dbName, userName, password, insertSql);
		
		/* update record. */
		String updateSql = "update teacher set name = 'jerry' where id = " + autoGenId;
		/* Execute the update command. */
		jdbcStatementExample.executeSql(ip, port, dbName, userName, password, updateSql);
		
		/* select records. */
		String selectSql = "select * from teacher";
		jdbcStatementExample.executeSelectSql(ip, port, dbName, userName, password, selectSql);
		
		String deleteSql = "delete from teacher where email = '[email protected]'";
		jdbcStatementExample.executeSql(ip, port, dbName, userName, password, deleteSql);
		
		/* select records after delete. */
		selectSql = "select * from teacher";
		jdbcStatementExample.executeSelectSql(ip, port, dbName, userName, password, selectSql);
	}

        	/* This method return java.sql.Connection object from MySQL server. */
	public Connection getMySqlConnection(String ip, int port, String dbName, String userName, String password)
	{
		/* Declare and initialize a sql Connection variable. */
		Connection ret = null;
		
		try
		{
		
			/* Register for mysql jdbc driver class. */
			Class.forName("com.mysql.jdbc.Driver");
			
			/* Create mysql connection url. */
			String mysqlConnUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbName;
			
			/* Get the mysql Connection object. */
			ret = DriverManager.getConnection(mysqlConnUrl, userName , password);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			return ret;
		}
	}

Output

java example output

Source Code:

  1. Download “JDBCStatementExamplesInsertDeleteUpdateSelectRecord.zip” JDBCStatementExamplesInsertDeleteUpdateSelectRecord.zip – Downloaded 92 times – 2 KB

(Visited 1,012 times, 5 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.