標準JDBCステップおよびjdbcバッチ
jdbc batch
jdbcにはbatch機能が含まれており、executeBatchメソッドを使用して一括操作を実現します.
コードクリップ:
1.Statementの使用
2.PreparedStatementの使用
MySQL and Java JDBC - Tutorial
Lars Vogel
Version 1.2
Copyright © 2009 Lars Vogel
19.07.2013
Revision History
Revision 0.1
25.05.2008
LarsVogel
created
Revision 0.2 - 1.2
14.09.2009 - 19.07.2013
LarsVogel
bug fixes and enhancements
MySQL and Java JDBC
This tutorial describes how to use Java JDBC to connect to MySQL and perform SQL queries, database inserts and deletes.
Table of Contents
1. Connection to database with Java
The interface for accessing relational databases from Java is Java Database Connectivity (JDBC). Via JDBC you create a connection to the database, issue database queries and updates and receive the results.
JDBC provides an interface which allows you to perform SQL operations independently of the instance of the used database. To use JDBC you require the database specific implementation of the JDBC driver.
2. Introduction to MySQL
To learn to install and use MySQL please see MySQL - Tutorial .
The following description will assume that you have successfully installed MySQL and know how to access MySQL via the command line.
3. MySQL JDBC driver
To connect to MySQL from Java you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J. You find the latest MySQL JDBC driver under the following URL: http://dev.mysql.com/downloads/connector/j .
The download contains a
4. Exercise: create example database
In this exercise you create a new database, a new user and an example table. For this connect to the MySQL server via the
Create a new database called feedback and start using it with the following command.
Create a user with the following command.
Now create a sample database table with example content via the following SQL statement.
5. Java JDBC
Create a Java project and a package called de.vogella.mysql.first.
Create a
Create the following class to connect to the MySQL database and perform queries, inserts and deletes. It also prints the metadata (table name, column names) of a query result.
Create the following main program to test your class.
6. Thank you
jdbc接続mysql:
jdbcにはbatch機能が含まれており、executeBatchメソッドを使用して一括操作を実現します.
void jdbc() throws Exception{
Connection conn = null;
PreparedStatement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");//
statement = conn.prepareStatement("insert into coures(id,name,age) values(?,?,?)");// PreparedStatement
conn.setSavepoint();//
conn.setAutoCommit(false);//
for(int i=0;i<100;i++){
statement.setInt(1, i);// 1
statement.setString(2, "tch");
statement.setInt(3, 23);
statement.addBatch();//
}
statement.executeBatch();//
conn.commit();// ,
} catch (Exception e) {
if(conn != null){
conn.rollback();// ,
}
e.printStackTrace();
}finally{//
if(statement != null){
statement.close();
}
if(conn != null){
conn.close();
}
}
}
コードクリップ:
1.Statementの使用
Connection conn = ConnectDBUtil.getConnection(); // Connection
conn.setAutoCommit(false); // false
Statement batchStat = conn.createStatement(); // Statement
for(int i=0 ; i<10000 ; i++){
String sql = "insert into test(id,name) values(" + i + ",'Jason')";
batchStat.addBatch(insert); // sql batch
}
batchStat.executeBatch(); // batch, batch sql
conn.commit();
2.PreparedStatementの使用
Connection conn = ConnectDBUtil.getConnection(); // Connection
conn.setAutoCommit(false); // false
PreparedStatement batchStat =
conn_manager.prepareStatement("insert into test(id,name) values(?,?)");
for(int i=0 ; i<10000 ; i++){
batchStat.setInt(1,i);
batchStat.setString(2,"Jason");
batchStat.addBatch();
}
batchStat.executeBatch(); // batch, batch sql
conn.commit();
MySQL and Java JDBC - Tutorial
Lars Vogel
Version 1.2
Copyright © 2009 Lars Vogel
19.07.2013
Revision History
Revision 0.1
25.05.2008
LarsVogel
created
Revision 0.2 - 1.2
14.09.2009 - 19.07.2013
LarsVogel
bug fixes and enhancements
MySQL and Java JDBC
This tutorial describes how to use Java JDBC to connect to MySQL and perform SQL queries, database inserts and deletes.
Table of Contents
1. Connection to database with Java
The interface for accessing relational databases from Java is Java Database Connectivity (JDBC). Via JDBC you create a connection to the database, issue database queries and updates and receive the results.
JDBC provides an interface which allows you to perform SQL operations independently of the instance of the used database. To use JDBC you require the database specific implementation of the JDBC driver.
2. Introduction to MySQL
To learn to install and use MySQL please see MySQL - Tutorial .
The following description will assume that you have successfully installed MySQL and know how to access MySQL via the command line.
3. MySQL JDBC driver
To connect to MySQL from Java you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J. You find the latest MySQL JDBC driver under the following URL: http://dev.mysql.com/downloads/connector/j .
The download contains a
JAR
file which we require later. 4. Exercise: create example database
In this exercise you create a new database, a new user and an example table. For this connect to the MySQL server via the
mysql
command line client. Create a new database called feedback and start using it with the following command.
create database feedback;
use feedback;
Create a user with the following command.
CREATE USER sqluser IDENTIFIED BY 'sqluserpw';
grant usage on *.* to sqluser@localhost identified by 'sqluserpw';
grant all privileges on feedback.* to sqluser@localhost;
Now create a sample database table with example content via the following SQL statement.
CREATE TABLE COMMENTS (id INT NOT NULL AUTO_INCREMENT,
MYUSER VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30),
WEBPAGE VARCHAR(100) NOT NULL,
DATUM DATE NOT NULL,
SUMMARY VARCHAR(40) NOT NULL,
COMMENTS VARCHAR(400) NOT NULL,
PRIMARY KEY (ID));
INSERT INTO COMMENTS values (default, 'lars', '[email protected]','http://www.vogella.com', '2009-09-14 10:33:11', 'Summary','My first comment');
5. Java JDBC
Create a Java project and a package called de.vogella.mysql.first.
Create a
lib
folder and copy the JDBC driver into this folder. Add the JDBC driver to your classpath. See Adding jars to the classpath for details. Create the following class to connect to the MySQL database and perform queries, inserts and deletes. It also prints the metadata (table name, column names) of a query result.
package de.vogella.mysql.first;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class MySQLAccess {
private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
public void readDataBase() throws Exception {
try {
// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager
.getConnection("jdbc:mysql://localhost/feedback?"
+ "user=sqluser&password=sqluserpw");
// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query
resultSet = statement
.executeQuery("select * from FEEDBACK.COMMENTS");
writeResultSet(resultSet);
// PreparedStatements can use variables and are more efficient
preparedStatement = connect
.prepareStatement("insert into FEEDBACK.COMMENTS values (default, ?, ?, ?, ? , ?, ?)");
// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
// Parameters start with 1
preparedStatement.setString(1, "Test");
preparedStatement.setString(2, "TestEmail");
preparedStatement.setString(3, "TestWebpage");
preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
preparedStatement.setString(5, "TestSummary");
preparedStatement.setString(6, "TestComment");
preparedStatement.executeUpdate();
preparedStatement = connect
.prepareStatement("SELECT myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
resultSet = preparedStatement.executeQuery();
writeResultSet(resultSet);
// Remove again the insert comment
preparedStatement = connect
.prepareStatement("delete from FEEDBACK.COMMENTS where myuser= ? ; ");
preparedStatement.setString(1, "Test");
preparedStatement.executeUpdate();
resultSet = statement
.executeQuery("select * from FEEDBACK.COMMENTS");
writeMetaData(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
}
private void writeMetaData(ResultSet resultSet) throws SQLException {
// Now get some metadata from the database
// Result set get the result of the SQL query
System.out.println("The columns in the table are: ");
System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
for (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
System.out.println("Column " +i + " "+ resultSet.getMetaData().getColumnName(i));
}
}
private void writeResultSet(ResultSet resultSet) throws SQLException {
// ResultSet is initially before the first data set
while (resultSet.next()) {
// It is possible to get the columns via name
// also possible to get the columns via the column number
// which starts at 1
// e.g. resultSet.getSTring(2);
String user = resultSet.getString("myuser");
String website = resultSet.getString("webpage");
String summary = resultSet.getString("summary");
Date date = resultSet.getDate("datum");
String comment = resultSet.getString("comments");
System.out.println("User: " + user);
System.out.println("Website: " + website);
System.out.println("Summary: " + summary);
System.out.println("Date: " + date);
System.out.println("Comment: " + comment);
}
}
// You need to close the resultSet
private void close() {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
}
Create the following main program to test your class.
package de.vogella.mysql.first.test;
import de.vogella.mysql.first.MySQLAccess;
public class Main {
public static void main(String[] args) throws Exception {
MySQLAccess dao = new MySQLAccess();
dao.readDataBase();
}
}
6. Thank you
jdbc接続mysql:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_Test {
//
static Connection conn;
static Statement st;
public static void main(String[] args) {
insert(); //
update(); //
delete(); //
query(); //
}
/* , */
public static void insert() {
conn = getConnection(); // ,
try {
String sql = "INSERT INTO staff(name, age, sex,address, depart, worklen,wage)"
+ " VALUES ('Tom1', 32, 'M', 'china','Personnel','3','3000')"; // sql
st = (Statement) conn.createStatement(); // sql Statement
int count = st.executeUpdate(sql); // sql ,
System.out.println(" staff " + count + " "); //
conn.close(); //
} catch (SQLException e) {
System.out.println(" " + e.getMessage());
}
}
/* , */
public static void update() {
conn = getConnection(); // ,
try {
String sql = "update staff set wage='2200' where name = 'lucy'";// sql
st = (Statement) conn.createStatement(); // sql Statement ,st
int count = st.executeUpdate(sql);// sql ,
System.out.println("staff " + count + " "); //
conn.close(); //
} catch (SQLException e) {
System.out.println(" ");
}
}
/* , */
public static void query() {
conn = getConnection(); // ,
try {
String sql = "select * from staff"; // sql
st = (Statement) conn.createStatement(); // sql Statement ,st
ResultSet rs = st.executeQuery(sql); // sql ,
System.out.println(" :");
while (rs.next()) { //
//
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
String address = rs.getString("address");
String depart = rs.getString("depart");
String worklen = rs.getString("worklen");
String wage = rs.getString("wage");
//
System.out.println(name + " " + age + " " + sex + " " + address
+ " " + depart + " " + worklen + " " + wage);
}
conn.close(); //
} catch (SQLException e) {
System.out.println(" ");
}
}
/* , */
public static void delete() {
conn = getConnection(); // ,
try {
String sql = "delete from staff where name = 'lili'";// sql
st = (Statement) conn.createStatement(); // sql Statement ,st
int count = st.executeUpdate(sql);// sql ,
System.out.println("staff " + count + "
"); //
conn.close(); //
} catch (SQLException e) {
System.out.println(" ");
}
}
/* */
public static Connection getConnection() {
Connection con = null; // Connection
try {
Class.forName("com.mysql.jdbc.Driver");// Mysql
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myuser", "root", "root");//
} catch (Exception e) {
System.out.println(" " + e.getMessage());
}
return con; //
}
}