`
dreamoftch
  • 浏览: 486234 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

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

jdbcbatch批处理 
阅读更多

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', 'myemail@gmail.com','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;	//返回所建立的数据库连接
	}
}

 

分享到：
| 在Struts2中使用ValueStack、ActionContex ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Spring.Batch批处理框架

    Spring Batch 是一个轻量级的、完善的批处理框架,旨在帮助企业建立健壮、高效的批处理应用。Spring Batch是Spring的一个子项目,使用Java语言并基于Spring框架为基础开发,使得已经使用 Spring 框架的开发者或者企业更...

    Spring Batch批处理框架

    Spring Batch批处理框架Spring Batch批处理框架Spring Batch批处理框架

    SpringBatch批处理框架

    资源名称：Spring Batch 批处理框架内容简介：《Spring Batch 批处理框架》全面、系统地介绍了批处理框架Spring Batch，通过详尽的实战示例向读者展示了Spring Batch框架对大数据批处理的基本开发能力，并对框架的...

    spring batch批处理 教程

    1，什么是批处理 3 2，什么是 Spring Batch 3 二，Spring Batch结构 4 1，Spring Batch体系结构 4 2，Spring Batch主要对象 5 三，Spring Batch流程介绍 5 四，Spring Batch之Step执行过程介绍 6 五，Spring Batch...

    SpringBatch批处理 刘相编

    基本篇重点讲述了数据批处理的核心概念、典型的作业配置、作业步配置，以及Spring Batch框架中经典的三步走策略：数据读、数据处理和数据写，详尽地介绍了如何对CVS格式文件、JSON格式文件、XML文件、数据库和JMS...

    Spring Batch批处理详解

    2024最新!一文看懂Spring Batch批处理(大白话版,干货满满), 学习你将收获: 一.系统了解Spring Batch批处理; 二.项目中能熟练使用Spring Batch批处理

    批处理batch

    批处理教程 batch 批处理简介

    java实现batch定时批处理

    用java springboot框架实现定时批处理，更新DB字段内容，更新成功后微信短信推送消息给用户

    Spring Boot整合Spring Batch，实现批处理

    Spring Boot整合Spring Batch的一个小例子，在网上发现这方面的资源比较少，特此将其上传供大家学习。

    Batch批处理框架.zip

    学习Spring Batch很好的资料。0基础到批处理大神，看这一个资料就够了。

    batch批处理显示汉字点阵

    汉字点阵输在生活中最常见的应用就是LED广告... 我使用c语言查找指定汉字在点阵字库中的位置并提取点阵信息，使用批处理来显示点阵文字【其中C查找点阵字库代码包含源码】 运行截图：http://pan.baidu.com/s/1o67dbEM

    springbatch 详解PDF附加 全书源码 压缩包

    spring batch批处理框架和对应的源码资源 rar 可以直接运行的

    批处理标准教程(bat入门到精通)

    批处理标准教程(bat入门到精通)。 批处理(Batch)，也称为批处理脚本，具有.bat 或者.cmd 的扩展名。顾名思义，批处理就是对某对象进行批量的处理，属于脚本语言的一种。

    批处理文件学习(batch file)

    压缩包内容： 简短介绍： Batch.pdf batch介绍.txt Batch Guide.pdf windiws批处理学习.pdf 书的影印版： _DOS批处理文件设计技巧.pdf _ms-dos批处理程序应用与技巧.pdf

    Spring家族的新成员Spring Batch批处理作业中间件.zip_中间件举例

    Spring家族的新成员Spring Batch批处理作业中间件.zip

    Haruka Batch Professional超级批处理开发环境。支持Exe编译。

    这个版本对于批处理的支持大幅度更新，以至于最后我想写每日提示的时候都想不起来更新了什么（更新的太多了……） 总之，请大家好好享用这个最后更新版吧……% 下载地址：...

    Simple Batch IDE 批处理语言的开发环境

    这是一个批处理程序的ide!小巧，也可以按照您的要求改变界面样式！ 虽然一些人觉得批处理不需要ide，但总是拿cmd和记事本编也不太爽快。 我就在网上找到了这款批处理的ide。 下载即用，绿色版。 只有16MB!

    利用批处理文件BatchFile进行后台数据库的恢复

    利用批处理文件BatchFile进行后台数据库的恢复。

Magicbox
Global site tag (gtag.js) - Google Analytics