JDBC

JDBC查询：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试JDBC查询
 * 
 * @author 30869
 * 
 */
public class JDBC_query {
	static Connection con = null;// 连接引用
	static Statement stmt = null;// 语句引用
	static ResultSet rs=null;//结果集引用
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf-8";//数据库地址和设置字符集
		String user = "root";
		String password = "123303";
		try {
			Class.forName("com.mysql.jdbc.Driver");//加载驱动类
			con = DriverManager.getConnection(url, user, password);//获取连接
			stmt=con.createStatement();//创建语句对象
			String sql="select * from student";//sql语句
			rs=stmt.executeQuery(sql);//执行查询，返回结果集
			while(rs.next()){//遍历结果集（迭代器），字段从1开始
				System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+"\t"+rs.getObject(4)+"\t"+
						rs.getObject(5)+"\t"+rs.getObject(6)+"\t"+rs.getObject(7)+"\t"+rs.getObject(8)+"\t"+
						rs.getObject(9)+"\t"+rs.getObject(10));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{//关闭资源
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}

JDBC更新：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试JDBC更新数据
 * 
 * @author 30869
 * 
 */
public class JDBC_update {
	static Connection con = null;//连接引用
	static Statement sta = null;//语句引用

	public static void main(String[] args) {
		String url = "jdbc:mysql://127.0.0.1:3306/myschool?useUnicode=true&characterEncoding=utf-8";
		String user = "root";
		String password = "123303";
		try {
			Class.forName("com.mysql.jdbc.Driver");//加载jdbc驱动
			con = DriverManager.getConnection(url, user, password);通过驱动程序类DriverManager创建连接
			String sql = "insert into `subject` "//sql语句
					+ "(`subjectName`,`classHour`,`gradeId`) "
					+ "values('dom',100,2)";
			sta = con.createStatement();//创建语句对象（通过连接对象创建）
			//增、删、改都用excuteUpdate()，查 用excuteQuery()
			int n = sta.executeUpdate(sql);//返回受影响的行数
			if (n == 1) {
				System.out.println("添加成功");	
			} else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (sta != null) {
					sta.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}

关于预编译sql语句接口PreparedStatement:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 测试预编译接口PreparedStatement 
 * 好处：提高代码的可读性、可维护性（避免sql语句拼接）、性能（创建PreparedStatement时sql语句作为参数，解析、预编译）、安全性（避免sql注入攻击）
 * 
 * @author 30869
 * 
 */
public class Use_PreparedStatement {
	static Connection con = null;
	static PreparedStatement pstmt = null;

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/epet?useUnicode=true&characterEncoding=utf-8";
		String user = "root";
		String password = "123303";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql="update dog set health=?,love=? where id=? ";
			pstmt=con.prepareStatement(sql);
			pstmt.setObject(1, 40);
			pstmt.setObject(2, 60);
			pstmt.setObject(3, 1);
			pstmt.executeUpdate();
			pstmt.setObject(1, 70);
			pstmt.setObject(2, 30);
			pstmt.setObject(3, 2);
			pstmt.executeUpdate();
			System.out.println("更新成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}

JDBC批处理:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 关于批处理 要点： 1、使用Statement(preparedStatement预编译空间有限，可能会抛异常) 2、手动提交事务
 * 
 * @author 30869
 * 
 */
public class Batch {
	static Connection con = null;// 声明连接和语句引用
	static Statement stmt = null;

	public static void main(String[] args) {
		String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8";
		String user = "root";
		String password = "123303";
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载驱动
			con = DriverManager.getConnection(url, user, password);// 通过驱动程序类DriverManager创建连接对象
			con.setAutoCommit(false);// 事务设置为手动提交
			stmt = con.createStatement();// 通过连接创建语句对象
			long start = System.currentTimeMillis();
			for (int i = 0; i < 20000; i++) {
				stmt.addBatch("insert into `tb_person`(`name`,`sex`) values('李林" + i + "','男')");
			}
			stmt.executeBatch();// 执行批处理语句
			con.commit();// 提交
			long end = System.currentTimeMillis();
			System.out.println("批处理执行时长：" + (end - start) + "毫秒");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}