Java JDBC 小例子

一直以来项目都是用Hibernate，JDBC反而没有正经练习过，现在将JDBC的基本使用做个小练习。

1. 使用MySQL建立一个test数据库，里面建立一个mytable表，3列（id，name，age）；将url, user, password配制成properties文件，放到 工程resource源代码包下面，这里将其命名为db_connect.properties文件

url = jdbc:mysql://localhost:3306/test
user = root
password = admin

2. 将数据库连接封装到一个类中，利用配置文件连接，静态返回connection

package study.jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnect {
	static String url;
	static String user;
	static String password;

	/**
	 * 获取一个JDBC连接，返回一个Connection对象
	 * @return connection
	 */
	public static Connection connectDB() {
		Connection connection = null;
		readProperties();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 读取properties文件，获取url,user,password
	 */
	private static void readProperties() {
		String fileName = "resouce/db_connect.properties"; //相对于工程
		Properties properties = new Properties();
		try {
			InputStream in = new FileInputStream(fileName);
			properties.load(in);
			in.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		url = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
	}

}

3.  对数据库进行增删改查的测试，主要练习使用PreparedStatement

package study.jdbc;

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

public class TestMain {
	public static void main(String[] args) {
		Connection connection = DBConnect.connectDB(); //获取数据库连接
		TestMain test = new TestMain();
		try { //测试
			test.clear(connection); 
			test.insert(connection); //增
			test.query(connection);
			System.out.println("----------");
			test.delete(connection); //删  
			test.query(connection);  
			System.out.println("----------");
			test.update(connection); //改
			test.query(connection);  //查
			connection.close(); //关闭数据库连接
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 使用PreparedStatement，效率高
	 * 动态执行SQL(带参数的SQL语句),是Statement子接口
	 * 对数据库进行insert，用带参数的语句批量插入
	 * @param connection
	 * @throws SQLException
	 */
	public void insert(Connection connection) throws SQLException {
		String sql = "INSERT INTO mytable(id,name,age) values (?,?,22);";
		PreparedStatement pr = connection.prepareStatement(sql);
		for (int i = 1; i <= 3; i++) {
			pr.setInt(1, i);
			pr.setString(2, "demo"+i);
			pr.executeUpdate();
		}
	}

	/**
	 * 对数据库进行delete
	 * @param connection
	 * @throws SQLException
	 */
	public void delete(Connection connection) throws SQLException {
		String sql = "delete from mytable where id=2;";
		PreparedStatement pr = connection.prepareStatement(sql);
		pr.executeUpdate();
	}

	/**
	 * 对数据库进行update
	 * @param connection
	 * @throws SQLException
	 */
	public void update(Connection connection) throws SQLException {
		String sql = "UPDATE mytable SET name='new' WHERE id=1;";
		PreparedStatement pr = connection.prepareStatement(sql); // 创建statement对象发送SQL到数据库
		pr.executeUpdate(); // 执行UPDATE

	}

	/**
	 * 查询query遍历结果集
	 * @param connection
	 * @throws SQLException
	 */
	public void query(Connection connection) throws SQLException {
		String sql = "select * from mytable;";
		PreparedStatement st = connection.prepareStatement(sql);
		ResultSet re = st.executeQuery(); // 查询，返回单个ResultSet对象
		while (re.next()) {
			int id = re.getInt(1);
			String name = re.getString(2);
			int age = re.getInt(3);
			System.out.println(id + "\t" + name + "\t" + age);
		}// 遍历结果集
	}
	
	/**
	 * 清空表
	 * @param connection
	 * @throws SQLException
	 */
	public void clear(Connection connection) throws SQLException {
		String sql = "delete from mytable;";
		PreparedStatement pr = connection.prepareStatement(sql);
		pr.executeUpdate();
	}
}