标准JDBC步骤以及jdbc batch 批处理

jdbc batch

博客分类：

 
• java

jdbcbatch 

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(?,?,?)");conn.setSavepoint();conn.setAutoCommit(false);for(int i=0;i<100;i++){statement.setInt(1, i);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(); //从自己写的工具类获取Connectionconn.setAutoCommit(false); //设定自动提交为falseStatement batchStat = conn.createStatement(); //创建Statementfor(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(); //从自己写的工具类获取Connectionconn.setAutoCommit(false); //设定自动提交为falsePreparedStatement 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 HistoryRevision 0.125.05.2008Lars
Vogel
createdRevision 0.2 - 1.214.09.2009 - 19.07.2013Lars
Vogel
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

2. Introduction to MySQL

3. MySQL JDBC driver

4. Exercise: create example database

5. Java JDBC

6. Thank you

7. Questions and Discussion

8. Links and Literature

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.

<iframe id="aswift_1" style="left: 0px; position: absolute; top: 0px;" name="aswift_1" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" width="728" height="90"></iframe>

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', '[e-mail]','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 + " 条数据\n");//输出删除操作的处理结果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;//返回所建立的数据库连接}}