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

jdbc batch

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	Lars Vogel	created
Revision 0.2 - 1.2	14.09.2009 - 19.07.2013	Lars 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;	//返回所建立的数据库连接
	}
}