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 - Create Table And Insert, Update, Delete Records

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, "READ :   How To Connect Oracle DB Use JDBC

Source Code

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

  • 58