JDBC PreparedStatement Example – Create Table And Insert, Update, Delete Records

java.sql.PreparedStatement is a more powerful database operation class than java.sql.Statement. You can send a pre-compiled SQL statement to the database server with specified parameters. It is more efficient than java.sql.Statement when executing similar SQL commands repeatedly. This article will show you how to use the java.sql.PreparedStatement object to implement create table, insert, update or delete records from MySQL Server.

1. java.sql.PreparedStatement Example Code Snippet.

1. Below is the example code snippet of how to use java.sql.PreparedStatement class.

   String sql = "create table .....";
   // Get db connection.
   java.sql.Connection dbConn = getConnection(.....);
   // Pre-compile the PreparedStatement object.
   java.sql.PreparedStatement pStmt = dbConn.prepareStatement(sql);
   // Set parameters in sql command.
   pStmt.setInt(.....);
   pStmt.setString(.....);
   // Execute the command.
   pStmt.executeUpdate();

2. The below example will use the MySQL Server teacher table.
   
3. java.sql.PreparedStatement create table example.

   /* Use PreparedStatement to create MySql table. */
   public void createTableUsePreparedStatement(String ip, int port, String dbName, String userName, String password)
   {
   	Connection dbConn = null;
   	PreparedStatement pStmt = null;
   	try
   	{
   		dbConn = this.getMySqlConnection(ip, port, dbName, userName, password);
   		
   		if(dbConn!=null)
   		{
   			StringBuffer sqlBuf = new StringBuffer();
   			
   			sqlBuf.append("CREATE TABLE `teacher` (");
   			sqlBuf.append("`name` varchar(100) NOT NULL,");
   			sqlBuf.append("`email` varchar(100) NOT NULL,");
   			sqlBuf.append("`id` int(11) NOT NULL AUTO_INCREMENT,");
                           sqlBuf.append("`registTime` datetime DEFAULT NULL,");				
                           sqlBuf.append("PRIMARY KEY (`id`)");
   			sqlBuf.append(") ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1");
   				
   			pStmt = dbConn.prepareStatement(sqlBuf.toString());
   			
   			pStmt.executeUpdate();
   			
   			System.out.println("Create db table success. ");
   		}
   	}catch(Exception ex)
   	{
   		ex.printStackTrace();
   	}finally
   	{
   		this.closeDBResource(pStmt, dbConn);
   	}
   }
   
   /* 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;
   	}
   }
   
   /* Close prepared statement and connection after use, this can avoid resource waste. */
   public void closeDBResource(PreparedStatement pStmt, Connection conn)
   {
   	try
   	{
   		if(pStmt!=null)
   		{
   			pStmt.close();
   			pStmt = null;
   		}
   		
           	if(conn!=null)
   		{
   			conn.close();
   			conn = null;
   		}
   	}catch(Exception ex)
   	{
   		ex.printStackTrace();
   	}
   }

4. java.sql.PreparedStatement insert record example.

   /* Use PreparedStatment to insert record into MySql table. */
   public void insertRecordsUsePreparedStatement(String ip, int port, String dbName, String userName, String password)
   {
   	Connection dbConn = null;
   	PreparedStatement pStmt = null;
   	try
   	{
   		dbConn = this.getMySqlConnection(ip, port, dbName, userName, password);
   		
   		if(dbConn!=null)
   		{
   			StringBuffer sqlBuf = new StringBuffer();
   			
   			sqlBuf.append("insert into `teacher` (name, email, registTime) values(?,?,?);");
   			
   			pStmt = dbConn.prepareStatement(sqlBuf.toString());
   			
   			pStmt.setString(1, "jerry");
   			
   			pStmt.setString(2, "[email protected]");
   			
                           // Get a java.util.Date object.
                           java.util.Date now = new java.util.Date();
                           // Convert the Date Object to java.sql.Timestamp object.
                           Timestamp ts = new java.sql.Timestamp(now.getTime());
                           // Set Timestamp value in MySql db.
                           pStmt.setTimestamp(3, ts);
                   	pStmt.executeUpdate();
   				
   			System.out.println("Insert record into table success. ");
   		}
   	}catch(Exception ex)
   	{
   		ex.printStackTrace();
   	}finally
   	{
   		this.closeDBResource(pStmt, dbConn);
   	}
   }

5. java.sql.PreparedStatement update record example.

   /* Use PreparedStatment to update record in MySql table. */
   public void updateRecordsUsePreparedStatement(String ip, int port, String dbName, String userName, String password)
   {
   	Connection dbConn = null;
   	PreparedStatement pStmt = null;
   	try
   	{
   		dbConn = this.getMySqlConnection(ip, port, dbName, userName, password);
   		
   		if(dbConn!=null)
   		{
   			StringBuffer sqlBuf = new StringBuffer();
   			
   			sqlBuf.append("update `teacher` set email = ? where name = ?");
   			
   			pStmt = dbConn.prepareStatement(sqlBuf.toString());
   			
   			pStmt.setString(1, "[email protected]");
   			
   			pStmt.setString(2, "jerry");
   			
   			pStmt.executeUpdate();
   			
   			System.out.println("Update record in table success. ");
   		}
   	}catch(Exception ex)
   	{
   		ex.printStackTrace();
   	}finally
   	{
   		this.closeDBResource(pStmt, dbConn);
   	}
   }

6. java.sql.PreparedStatement delete records example.

   /* Use PreparedStatment to delete record in MySql table. */
   public void deleteRecordsUsePreparedStatement(String ip, int port, String dbName, String userName, String password)
   {
   	Connection dbConn = null;
   	PreparedStatement pStmt = null;
   	try
   	{
   		dbConn = this.getMySqlConnection(ip, port, dbName, userName, password);
   		
   		if(dbConn!=null)
   		{
   			StringBuffer sqlBuf = new StringBuffer();
   			
   			sqlBuf.append("delete from `teacher` where name = ?");
   			
   			pStmt = dbConn.prepareStatement(sqlBuf.toString());
   			
   			pStmt.setString(1, "jerry");
   			
   			pStmt.executeUpdate();
   			
   			System.out.println("delete record in table success. ");
   		}
   	}catch(Exception ex)
   	{
   		ex.printStackTrace();
   	}finally
           {
   		this.closeDBResource(pStmt, dbConn);
   	}
   }

7. Main method.

   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 = "";
   		
   	PreparedStatementExample example = new PreparedStatementExample();
   		
   	example.createTableUsePreparedStatement(ip, port, dbName, userName, password);
   	example.insertRecordsUsePreparedStatement(ip, port, dbName, userName, password);
   	example.updateRecordsUsePreparedStatement(ip, port, dbName, userName, password);
   	example.deleteRecordsUsePreparedStatement(ip, port, dbName, userName, password);
   }