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 execute similar sql commands repeatedly. This article will show you how to use java.sql.PreparedStatement to implement create table, insert, update or delete records from MySQL Server.

Code Example Snippet

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();

Below example will use MySql Server teacher table.

jdbc preparedstatement example teacher table

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();
		}
	}

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);
		}
	}

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);
		}
	}

PreparedStatement Delete Record 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);
		}
	}

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);
	}

 

READ :   How To Get Database Meta Data

Source Code

  1. Download “JDBCPreparedStatementExample.zip” JDBCPreparedStatementExample.zip – Downloaded 99 times – 1 KB

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.