Java MySQL INSERT example: Can you share a Java MySQL INSERT example, specifically using a Java PreparedStatement object?
Sure. As the question implies, you can use the Java Statement
class to issue a MySQL INSERT statement, but the Java PreparedStatement
class provides a much better way to insert data into a MySQL database table. (You can learn more about the Java Statement
class in this Using SQL INSERT with Java Statement tutorial.
Java INSERT - An example MySQL database table
The first thing we'll need is an example MySQL database table. To keep it simple, but to also show several different MySQL data types, I've created the following MySQL example database table:
create
table
users (
id
int
unsigned auto_increment
not
null
,
first_name
varchar
(32)
not
null
,
last_name
varchar
(32)
not
null
,
date_created
timestamp
default
now(),
is_admin boolean,
num_points
int
,
primary
key
(id)
);
|
A few of these MySQL fields are a little contrived, but I wanted to show several different data types in one table, so this is what I came up with. In particular, the field "num_points" is a little weird. I made it up so I could show a MySQL int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.
Other than that, this MySQL database table is relatively normal, though it is greatly simplified.
Java MySQL INSERT PreparedStatement example - Source code
Given that MySQL database table design, let's assume that we just want to insert one record into this MySQL table. To do so, we just need to follow these steps:
- Create a Java Connection to our example MySQL database.
- Create a SQL INSERT statement, using the Java
PreparedStatement
syntax. - Set the fields on our Java
PreparedStatement
object. - Execute a Java
PreparedStatement
. - Close our Java MYSQL database connection.
- Catch any SQL exceptions that may come up during the process.
I've tried to document the following Java MySQL INSERT example so you can see these steps. Note that in this example my MySQL database username is "root", my password is blank, and the MySQL database is running on the same computer where this program is run, so the database host name is "localhost".
import
java.sql.*;
import
java.util.Calendar;
/**
* A Java MySQL PreparedStatement INSERT example.
* Demonstrates the use of a SQL INSERT statement against a
* MySQL database, called from a Java program, using a
* Java PreparedStatement.
*
* Created by Alvin Alexander, <a href="http://devdaily.com" title="http://devdaily.com">http://devdaily.com</a>
*/
public
class
JavaMysqlPreparedStatementInsertExample
{
public
static
void
main(String[] args)
{
try
{
// create a mysql database connection
String myDriver =
"org.gjt.mm.mysql.Driver"
;
Class.forName(myDriver);
Connection conn = DriverManager.getConnection(myUrl,
"root"
,
""
);
// create a sql date object so we can use it in our INSERT statement
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate =
new
java.sql.Date(calendar.getTime().getTime());
// the mysql insert statement
String query =
" insert into users (first_name, last_name, date_created, is_admin, num_points)"
+
" values (?, ?, ?, ?, ?)"
;
// create the mysql insert preparedstatement
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString (
1
,
"Barney"
);
preparedStmt.setString (
2
,
"Rubble"
);
preparedStmt.setDate (
3
, startDate);
preparedStmt.setBoolean(
4
,
false
);
preparedStmt.setInt (
5
,
5000
);
// execute the preparedstatement
preparedStmt.execute();
conn.close();
}
catch
(Exception e)
{
System.err.println(
"Got an exception!"
);
System.err.println(e.getMessage());
}
}
}
|
Java MySQL INSERT example - results
After running this Java MySQL INSERT example program, when you query your MySQL database table like this:
select
*
from
users;
|
you should see some output like this:
+----+------------+-----------+---------------------+----------+------------+ | id | first_name | last_name | date_created | is_admin | num_points | +----+------------+-----------+---------------------+----------+------------+ | 1 | Barney | Rubble | 2010-06-23 14:02:00 | 0 | 5000 | +----+------------+-----------+---------------------+----------+------------+ 1 row in set (0.00 sec)
This shows that the Java MySQL INSERT PreparedStatement
example worked properly.
Java MySQL INSERT example using PreparedStatement - summary
I hope this Java MySQL INSERT example (using a PreparedStatement
) makes sense as is. In "real world" Java database programs I almost always use Spring to access a database, but when you're first getting started, I think it's important to see examples like this so you can understand how things work under the covers. This example specifically shows:
- How to connect to a MySQL database.
- How to create a Java
Date
object. - How to create a Java
PreparedStatement
with a SQL INSERT query. - How to set the fields on a variety of different data types.
- How to execute the Java
PreparedStatement
. - One way to confirm that our data was successfully inserted into our MySQL database.
As usual, if you have any questions or comments about this Java MySQL INSERT example, just use the Comment form below.
Reference: http://alvinalexander.com/java/java-mysql-insert-example-preparedstatement