JDBC操作Mysql数据库

工作用到的数据库挺多， 常用已涉及sql server，oracle ，mysql 。 这些操作数据库功能类似又不尽相同， 现记录下Java操作Mysql的样例。本实例主要使用参数化查询sql，或调用存储过程返回数据

1  数据准备创建数据表， 添加数据。   

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `Code` VARCHAR(20) DEFAULT NULL,
  `Name` VARCHAR(20) DEFAULT NULL,
  `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t1(`Code`,`Name`) VALUES('cjr','陈佳仁');

2 导入包

导入  mysql-connector-java-5.1.39-bin.jar   包下载地址

3 添加一个简易Jdbc帮助类，实现获取连接，关闭连接功能。

package cjr.util;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
	private static String connectString="jdbc:mysql://localhost:3306/test0629?characterEncoding=utf8&useSSL=true";
	private static String user ="root";
	private static String pwd ="";
	private static String driver ="com.mysql.jdbc.Driver";
	
	static{
		String path = Class.class.getClass().getResource("/").getPath()+"db.properties";
		Properties pro = new Properties();
		FileInputStream in;
		try {
			in = new FileInputStream(path);
			pro.load(in);
			driver = pro.getProperty("driver");
			connectString = pro.getProperty("connection");
			user = pro.getProperty("user");
			pwd = pro.getProperty("pwd");
			Class.forName(driver);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("error");
		}
	}
	public static Connection getConnection(){
		Connection con=null;
		try {
			con = DriverManager.getConnection(connectString, user, pwd);
		} catch (Exception e) {
			System.out.println("error getConnection");
		}
		return con;
	}
	
	public static void close(Connection con,Statement stmt,ResultSet rs){
		if(rs!=null)
			try {
				rs.close();
				rs=null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if(stmt!=null)
			try {
				stmt.close();
				stmt=null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		try {
			if(con!=null&&!con.isClosed())
				try {
					con.close();
					con=null;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(Connection con,Statement stmt){
		close(con, stmt, null);
	}
}

4  Jdbc操作

4.1 新增数据，并返回新增的id

<span style="white-space:pre">	</span>@Test
	public void userAdd(){
		String code = "zs";
		String name = "张三";
		String sql = "INSERT INTO t1(`Code`,`Name`) VALUES(?,?)";
		Connection con = null;
		PreparedStatement pstmt = null;
		int id;
		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, code);
			pstmt.setString(2, name);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			if(rs.next()){
				 id = rs.getInt(1);
				 System.out.println("插入的数据为："+id+","+code+","+name);
			}	
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

输出结果：

插入的数据为：2,zs,张三

4.2 修改操作

<span style="white-space:pre">	</span>@Test
	public void userEdit(){
		String code = "cjh";
		String name = "陈津海";
		int user_id = 1;
		String sql = "UPDATE t1 SET CODE=?,NAME=?,TIME=NOW() WHERE id = ?";
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, code);
			pstmt.setString(2, name);
			pstmt.setInt(3, user_id);
			/**
			* 参数可以通过Object类型设置简单参数
			pstmt.setObject(1, code);
			pstmt.setObject(2, name);
			pstmt.setObject(3, user_id);*/
			
			int cnt = pstmt.executeUpdate();
			System.out.println("成功执行了" + cnt + "行");
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

运行结果：

成功执行了1行

4.3 删除操作

	@Test
	public void userDel(){
		int user_id = 8;
		String sql = "delete from t1 WHERE id = ?";
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user_id);
			int cnt = pstmt.executeUpdate();
			System.out.println("成功删除了" + cnt + "行");
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

运行结果：

成功删除了1行

4.4 通过sql查询操作

<span style="white-space:pre">	</span>@Test
	public void getUserById(){
		int user_id = 2;
		String sql = "select * from t1 WHERE id = ?";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				System.out.println("用户信息："
						+ rs.getInt("id")+","
						+ rs.getString("CODE")+","
						+ rs.getString("Name")+","
						+ rs.getDate("Time") );
			}
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

运行结果：

用户信息：2,zs,张三,2016-06-29

4.4 通过存储过程查询操作

1 创建存储过程 ，含输出参数的存储过程

DROP PROCEDURE IF EXISTS Get_user_list ;
DELIMITER $$
CREATE  PROCEDURE Get_user_list(p_id INT,OUT p_count INT)    #含输出参数
BEGIN
	SELECT COUNT(*) INTO p_count FROM t1 WHERE id >= p_id ;
	SELECT * FROM t1 WHERE id >= p_id;
END $$

2 在查询编辑器调用存储过程

CALL Get_user_list(1,@p_count);
SELECT @p_count;

3 java代码操作

<span style="white-space:pre">	</span>@Test
	public void getUserList(){
		int p_id=1;
		Connection con = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			cstmt = con.prepareCall("call Get_user_list(?,?)");
			cstmt.setInt(1, p_id);
			cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
			rs = cstmt.executeQuery();
			System.out.println("总记录行数："+ cstmt.getInt(2));
			while(rs.next()){
				System.out.println("用户信息："
						+ rs.getInt("id")+","
						+ rs.getString("CODE")+","
						+ rs.getString("Name")+","
						+ rs.getDate("Time") );
			}
		} catch (Exception e) {
			System.out.println("getUserList error");
		}finally{
			JdbcUtil.close(con, cstmt, rs);
		}
	}

运行结果：

总记录行数：2
用户信息：1,cjr,陈佳仁,2016-06-29
用户信息：2,zs,张三,2016-06-29

4.5 批量操作

<pre name="code" class="java">	@Test
	public void userBatchAdd(){
		String code = "ls";
		String name = "李四";
		String sql = "INSERT INTO t1(`Code`,`Name`) VALUES(?,?)";
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			for(int i=0;i<10;i++){
				pstmt.setString(1, code+i);
				pstmt.setString(2, name+i);
				pstmt.addBatch(); 
			}
			pstmt.executeBatch();
			System.out.println("批量插入成功");
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

运行结果：

批量插入成功

可在数据库查看新增了10条数据，   批量操作的只执行一次executeBatch()。

4.6 事务操作

连接Connection默认是自动提交的，需将其设置为手动提交

<span style="white-space:pre">	</span>@Test
	public void userDelTransation(){
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = JdbcUtil.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("delete from t1 where id = 7");
			pstmt.executeUpdate();
			System.out.println("执行第一条语句成功！");
			pstmt = con.prepareStatement("delete1 form t1 where id = 6");
			pstmt.executeUpdate();
			System.out.println("执行第二条语句成功！");
			con.commit();
			System.out.println("执行成功并提交");
		} catch (Exception e) {
			try {
				if(con != null){
					con.rollback();
					System.out.println("执行失败，已回滚");
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			JdbcUtil.close(con, pstmt);
		}
	}

运行结果

执行第一条语句成功！
执行失败，已回滚

备注： 第一条语句执行成功，第二条语句执行失败， 因此数据被回滚。  可以修改第二条sql语句：delete from t1 where id = 6

执行成功并提交数据。

设置参数时： pstmt.setObject(1, code);   可以通过setObject方法设置int ,String 等参数