{"id":3481,"date":"2015-05-16T17:21:41","date_gmt":"2015-05-17T01:21:41","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3481"},"modified":"2024-01-11T02:16:27","modified_gmt":"2024-01-11T09:16:27","slug":"mysql-jdbc-insert","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/mysql-jdbc-insert\/","title":{"rendered":"Inserting Data into Table using JDBC PreparedStatement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use a PreparedStatement<\/code> object to insert data into a table.<\/p>\n\n\n\n

This tutorial picks up where the Updating Data in MySQL Using JDBC PreparedStatement<\/a> tutorial left off.<\/p>\n\n\n\n

The following defines a new method called add()<\/code> in the Candidate<\/code> class that inserts a new row<\/a> into the candidates<\/code> table:<\/p>\n\n\n

import<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;\n\npublic<\/span> class<\/span> Candidate<\/span> <\/span>{\n    public<\/span> static<\/span> int<\/span> add<\/span>(String firstName,String lastName,Date dob, String email, String phone)<\/span> <\/span>{\n        int<\/span> id = 0<\/span>;\n\n        String sql = \"INSERT INTO candidates(first_name,last_name,dob,phone,email) VALUES(?,?,?,?,?)\"<\/span>;\n\n        try<\/span> (var<\/span> conn = MySQLConnection.connect();\n             var<\/span> stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {\n\n            \/\/ set parameters for statement<\/span>\n            stmt.setString(1<\/span>, firstName);\n            stmt.setString(2<\/span>, lastName);\n            stmt.setDate(3<\/span>, dob);\n            stmt.setString(4<\/span>, phone);\n            stmt.setString(5<\/span>, email);\n\n            int<\/span> rowAffected = stmt.executeUpdate();\n            if<\/span>(rowAffected == 1<\/span>)\n            {\n                \/\/ get candidate id<\/span>\n                var<\/span> rs = stmt.getGeneratedKeys();\n                if<\/span>(rs.next()){\n                    id = rs.getInt(1<\/span>);\n                }\n            }\n        } catch<\/span> (SQLException e) {\n            e.printStackTrace();\n        }\n\n        return<\/span> id;\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the SQLException<\/code>, Date<\/code>, and Statement<\/code> classes from the java.sql<\/code> package:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define the add()<\/code> method that accepts six parameters that correspond to the columns of the candidates<\/code> table:<\/p>\n\n\n
public<\/span> static<\/span> int<\/span> add<\/span>(String firstName,String lastName,Date dob, String email, String phone)<\/span> <\/span>{<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, declare the variable id<\/code> and initialize its value to zero:<\/p>\n\n\n
int<\/span> id = 0<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, define an INSERT<\/code> statement that inserts a new row into the candidates<\/code> table:<\/p>\n\n\n
String sql = \"INSERT INTO candidates(first_name,last_name,dob,phone,email) VALUES(?,?,?,?,?)\"<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, establish a connection by connecting to the database and prepare the SQL statement for execution: <\/p>\n\n\n
try<\/span> (var<\/span> conn = MySQLConnection.connect();\n     var<\/span> stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, set the parameters:<\/p>\n\n\n
stmt.setString(1<\/span>, firstName);\nstmt.setString(2<\/span>, lastName);\nstmt.setDate(3<\/span>, dob);\nstmt.setString(4<\/span>, phone);\nstmt.setString(5<\/span>, email);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, execute the statement:<\/p>\n\n\n
int<\/span> rowAffected = stmt.executeUpdate();<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Eighth, get the last inserted id:<\/p>\n\n\n
if<\/span>(rowAffected == 1<\/span>) {\n    \/\/ get candidate id<\/span>\n    var<\/span> rs = stmt.getGeneratedKeys();\n    if<\/span>(rs.next()) {\n        id = rs.getInt(1<\/span>);\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Ninth, return the of the inserted candidate:<\/p>\n\n\n
return<\/span> id;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows how to call the add() method in the Main program to insert a new row into the candidates table:<\/p>\n\n\n
import<\/span> java.sql.Date;\n\npublic class<\/span> Main<\/span> <\/span>{\n    public static<\/span> void<\/span> main(String<\/span>[] args){\n        int id = Candidate.add(\"Jane\"<\/span>, \"Smith\"<\/span>, Date<\/span>.valueOf(\"1982-12-31\"<\/span>),\"smith.j@gmail.com\"<\/span>,\"(408)-111-2222\"<\/span>);\n        System.out.println(\"The inserted id is \"<\/span> + id);\n    }\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
The inserted id is 134<\/code><\/span><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the PreparedStatement<\/code> object to insert a new row into a table and obtain the inserted ID.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t