连接mysql数据库，寻求改进建议

[尘缘udbwcso]

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

/**
 * database DEFAULT CHARACTER SET utf8
 * 
 */

public final class JdbcUtil {

	private static final String url = "jdbc:mysql://localhost:3306/dbdemo?useUnicode=true&characterEncoding=UTF-8";
	private static final String user = "root";
	private static final String password = "sa";

	/**
	 * 注册
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection register(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public static void freeResource(Connection connection, Statement statement,
			ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}



public class BaseDao {
		
	/**
	 * 执行sql语句,如果第一个结果是 ResultSet 对象则返回List
	 * @param sql
	 * @return
	 */
	public List execute(String sql, List params){
		if (sql == null || sql.equals("")) {
			return null;
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			connection = JdbcUtil.register();
			connection.setAutoCommit(false);// 关闭自动提交事务（开启事务）
			preparedStatement = connection.prepareStatement(sql);
			if(params != null && params.size() > 0){
				for(int i = 0; i < params.size(); ++i){
					preparedStatement.setObject(i + 1, params.get(i));
				}
			}
			flag = preparedStatement.execute();
			connection.commit();
			if(flag){
				rs = preparedStatement.getResultSet();
				List list = convertToList(rs);
				return list;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.freeResource(connection, preparedStatement, rs);
		}
		return null;
	}
	
	private List convertToList(ResultSet rs) throws SQLException {
		List list = new ArrayList();
		// Get the element of resultSet
		ResultSetMetaData md = rs.getMetaData();
		// Map rowData;
		int columnCount = md.getColumnCount();
		// Use Map
		while (rs.next()) {
			Map rowData = new HashMap();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}
}




public class BaseService {
	private BaseDao baseDao = new BaseDao();
	
		
	public List queryForList(String sql, List params){
		List list = baseDao.execute(sql, params);
		return list;
	}
	
	public void execute(String sql, List params){
		baseDao.execute(sql, params);
	}
	
		
	public static void main(String[] args){
		BaseService baseService = new BaseService();

		String sql = "select * from user where id > 1";
		 List list = baseService.queryForList(sql, null);
		for (int i = 0; i < list.size(); ++i) {
			Map map = (Map) list.get(i);
			System.out.println(map.get("name"));
		}
		
//		sql = "update user set name=? where id=?";
//		list = new ArrayList();
//		list.add("udb");
//		list.add("92");
//		baseService.execute(sql, list);
	}
}

[尘缘udbwcso]

[疯狂熊猫人]

如果像你原来那样写，每次都需要创建connection，这是非常浪费时间的，也就是效率问题。

[尘缘udbwcso]

public final class JdbcUtil {

	private static final String url = "jdbc:mysql://localhost:3306/dbdemo?useUnicode=true&characterEncoding=UTF-8";
	private static final String user = "root";
	private static final String password = "sa";

	private static ThreadLocal<Connection> connectionContainer = new ThreadLocal<Connection>();

	/**
	 * 注册
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection register() {
		Connection connection = connectionContainer.get();
		try {
			if (connection == null) {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				connection = DriverManager.getConnection(url, user, password);
			}
			connectionContainer.set(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public static void freeResource(Statement statement, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (statement != null) {
				statement.close();
			}
			Connection connection = connectionContainer.get();
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			connectionContainer.remove();
		}
	}
}

[咖啡不放糖]

哈哈，马克一下，这学期我也在学JAVA~

[forqiao]

学习了 这个是不是那个连接池的模式啊 ！

[疯狂熊猫人]

创建连接的时候，可以使用ThreadLocal进行绑定，一个线程一个connection。

[尘缘udbwcso]

引用 1 楼 crazypandariy 的回复:

创建连接的时候，可以使用ThreadLocal进行绑定，一个线程一个connection。

那么该怎么写呢？

[疯狂熊猫人]

引用 2 楼 udbwcso 的回复:

[quote=引用 1 楼 crazypandariy 的回复:] 创建连接的时候，可以使用ThreadLocal进行绑定，一个线程一个connection。

那么该怎么写呢？[/quote]

private static ResourceBundle rb = null ;
	private static ThreadLocal<Connection> tconn = new ThreadLocal<Connection>() ;
	
	static{
		rb = ResourceBundle.getBundle("dbconfig");
		try {
			Class.forName(rb.getString("driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		Connection conn = tconn.get();
		try {
			if(conn==null){
				conn = DriverManager.getConnection(rb.getString("url"), 
						rb.getString("user"), rb.getString("pwd"));
				tconn.set(conn);
			}
		}  catch (SQLException e) {
			e.printStackTrace();
		}
		return conn ;
	}

这是学java的时候写的。现在都好久不用手写jdbc连接了。

[尘缘udbwcso]

引用 3 楼 crazypandariy 的回复:

[quote=引用 2 楼 udbwcso 的回复:] [quote=引用 1 楼 crazypandariy 的回复:] 创建连接的时候，可以使用ThreadLocal进行绑定，一个线程一个connection。

那么该怎么写呢？[/quote]

private static ResourceBundle rb = null ;
	private static ThreadLocal<Connection> tconn = new ThreadLocal<Connection>() ;
	
	static{
		rb = ResourceBundle.getBundle("dbconfig");
		try {
			Class.forName(rb.getString("driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		Connection conn = tconn.get();
		try {
			if(conn==null){
				conn = DriverManager.getConnection(rb.getString("url"), 
						rb.getString("user"), rb.getString("pwd"));
				tconn.set(conn);
			}
		}  catch (SQLException e) {
			e.printStackTrace();
		}
		return conn ;
	}

这是学java的时候写的。现在都好久不用手写jdbc连接了。[/quote]

public final class JdbcUtil {

	private static final String url = "jdbc:mysql://localhost:3306/dbdemo?useUnicode=true&characterEncoding=UTF-8";
	private static final String user = "root";
	private static final String password = "sa";

	private static ThreadLocal<Connection> connThread = new ThreadLocal<Connection>();

	/**
	 * 注册
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection register() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, user, password);
			connThread.set(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public static void freeResource(Statement statement, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (statement != null) {
				statement.close();
			}
			Connection connection = connThread.get();
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

是不是这么写？这么写有什么好处呢？