jdbc链接mysql的第二个例子。使用properties文件存放连接信息。使用jar文件一个

该例子将jdbc的链接信息封装到properties文件中。并将jdbc的基本使用框架提取到一个jdbcUtils类中。简化了jdbc操作数据库的操作。

JdbcUtil工具类：

package com.xiaohui.jdbc;

import java.io.IOException;
import java.io.InputStream;
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 final class JdbcUtil {
	private static String url;//数据库链接字符串
	private static String user;//用户名
	private static String pass;//密码
	private static String driver;//数据库驱动
	//加载类时，先将jdbc链接数据库信息获取并赋值
	static {
		Properties prop = new Properties();
		InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("com/xiaohui/jdbc/info.properties");
		try {
			prop.load(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
		url = prop.getProperty("url");
		user = prop.getProperty("user");
		pass = prop.getProperty("pass");
		driver = prop.getProperty("driver");
	}
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//取得链接
	public static Connection getMySqlConnection(){
		Connection conn=null;
		try {
			conn = DriverManager.getConnection(url,user,pass);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	//关闭结果集
	public static  void close(ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//关闭封装SQl命令的对象
	public static  void close(Statement state){
		if(state!=null){
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//关闭链接
	public static  void close(Connection conn){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 当很很多用户同时操作数据库时，获取到的都是一个conn链接对象。这样肯定不行。为了让每个线程都对应唯一的conn的链接对象可对上面的工具类做如下修改：

package com.xiaohui.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
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 final class JdbcUtil {
	private static String url;
	private static String user;
	private static String pass;
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	private static String driver;
	static {
		Properties prop = new Properties();
		InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream(
				"com/xiaohui/jdbc/config/info.properties");
		try {
			prop.load(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
		url = prop.getProperty("url");
		user = prop.getProperty("user");
		pass = prop.getProperty("pass");
		driver = prop.getProperty("driver");
	}
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 让每个线程都对应唯一的一个Connection 对象
	public static Connection getMySqlConnection() {
		Connection conn = tl.get();
		if(conn==null){
			//第一次启动该线程没有绑定conn 
			try {
				conn = DriverManager.getConnection(url, user, pass);
				//将conn绑定到tl中。
				tl.set(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		return conn;
	}

	public static void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(Statement state) {
		if (state != null) {
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void begin() {
		Connection conn = getMySqlConnection();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void commit() {
		Connection conn = getMySqlConnection();
		try {
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void rollback() {
		Connection conn = getMySqlConnection();
		try {
			conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void closeConnection() {
		Connection conn = getMySqlConnection();
		JdbcUtil.close(conn);
		//将该线程与所绑定的Connection对象分离
		tl.remove();
	}
}

 

 

 

简单的测试一：

package com.xiaohui.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo3 {
	public static void main(String[] args) throws ClassNotFoundException {
		Connection conn = null;
		Statement state = null;
		ResultSet rs = null;
		String sqlstr = "select * from employee";
		try {
			conn = JdbcUtil.getMySqlConnection();
			state = conn.createStatement();
			rs = state.executeQuery(sqlstr);
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				float salary = rs.getFloat("salary");
				System.out.println(id + ":" + name + ":" + sex + ":" + salary);
			}
		} catch (Exception e) {
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(state);
			JdbcUtil.close(conn);
		}
	}
}

配置文件：info.properties

url = jdbc:mysql://127.0.0.1:3306/mysql3
user = root
pass = root
driver =com.mysql.jdbc.Driver

 

使用到的jar文件：mysql-connector-java-5.1.22-bin.jar