JDBC工具类DataSourceUtils，dao接口代码示例；

最新推荐文章于 2025-06-17 00:09:15 发布

原创最新推荐文章于 2025-06-17 00:09:15 发布 · 5.3k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#JDBC #DataSourceUtils #DataSource #dao接口代码示例 #dao

java 专栏收录该内容

347 篇文章

订阅专栏

本文介绍了一个基于Druid连接池的DataSourceUtils工具类实现，该工具类提供了数据库连接管理的方法，包括获取连接、开启事务、回滚、提交等操作，并展示了在DAO层的具体应用。

DataSourceUtils工具类连接池用的是Druid：

import com.igeek.common.utils.DataSourceUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DataSourceUtils {

	private static DataSource dataSource ;

	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	
	static{
		Properties properties = new Properties();
		try {
			properties.load(DataSourceUtils.class.getResourceAsStream("/db.properties"));
			dataSource = DruidDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 直接可以获取一个连接池
	public static DataSource getDataSource() {
		return dataSource;
	}

	// 获取连接对象
	public static Connection getConnection() throws SQLException {

		Connection con = tl.get();
		if (con == null) {
			con = dataSource.getConnection();
			tl.set(con);
		}
		return con;
	}

	// 开启事务
	public static void startTransaction() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.setAutoCommit(false);
		}
	}

	// 事务回滚
	public static void rollback() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.rollback();
		}
	}

	// 提交并且 关闭资源及从ThreadLocall中释放
	public static void commitAndRelease() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.commit(); // 事务提交
			con.close();// 关闭资源
			tl.remove();// 从线程绑定中移除
		}
	}

	// 关闭资源方法
	public static void closeConnection() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.close();
            tl.remove();// 从线程绑定中移除
		}
	}

	public static void closeStatement(Statement st) throws SQLException {
		if (st != null) {
			st.close();
		}
	}

	public static void closeResultSet(ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}

}

dao应用：

package com.igeek.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.igeek.common.utils.DataSourceUtils;
import com.igeek.shop.dao.UserDao;
import com.igeek.shop.entity.User;

/**
 * 
 * TODO
 *
 * 2018年9月1日上午10:26:41
 */
public class UserDaoImpl implements UserDao {

	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	@Override
	public int save(User u) {
		int result=0;
		String sql = "insert into user(uid,username,password,name,email,telephone,birthday,sex,state,code) values(?,?,?,?,?,?,?,?,?,?)";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, u.getUid());
			pst.setString(2, u.getUsername());
			pst.setString(3, u.getPassword());
			pst.setString(4, u.getName());
			pst.setString(5, u.getEmail());
			pst.setString(6, u.getTelephone());
			pst.setString(7, u.getBirthday());
			pst.setString(8, u.getSex());
			pst.setInt(9, u.getState());
			pst.setString(10, u.getCode());
			
			result = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return result;
	}
	/* (non-Javadoc)
	 * @see com.igeek.shop.dao.UserDao#updateUserState(com.igeek.shop.entity.User)
	 */
	@Override
	public int updateUserState(User u) {
		int result=0;
		String sql = "update user set state = ? where code=?";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, u.getState());
			pst.setString(2, u.getCode());
			result = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return result;
	}
	/* (non-Javadoc)
	 * @see com.igeek.shop.dao.UserDao#queryByUsername(java.lang.String)
	 */
	@Override
	public User queryByUsername(String username) {
		User user = null;
		String sql = "select * from user where username = ?";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			rs = pst.executeQuery();
			if(rs.next()){
				user = new User();
				user.setBirthday(rs.getString("birthday"));
				user.setCode(rs.getString("code"));
				user.setEmail(rs.getString("email"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getString("sex"));
				user.setState(rs.getInt("state"));
				user.setTelephone(rs.getString("telephone"));
				user.setUid(rs.getString("uid"));
				user.setUsername(rs.getString("username"));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return user;
	}
	/* (non-Javadoc)
	 * @see com.igeek.shop.dao.UserDao#queryAll()
	 */
	@Override
	public List<User> queryAll() {
		List<User> users = new ArrayList<>();
		String sql = "select * from user";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setBirthday(rs.getString("birthday"));
				user.setCode(rs.getString("code"));
				user.setEmail(rs.getString("email"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getString("sex"));
				user.setState(rs.getInt("state"));
				user.setTelephone(rs.getString("telephone"));
				user.setUid(rs.getString("uid"));
				user.setUsername(rs.getString("username"));
				users.add(user);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return users;
	}

}

package com.igeek.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.igeek.common.utils.DataSourceUtils;
import com.igeek.shop.dao.ProductDao;
import com.igeek.shop.entity.Product;

/**
 * 
 * TODO
 *
 * 2018年9月3日下午6:36:31
 */
public class ProductDaoImpl implements ProductDao {

	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	public List<Product> findHotProducts() {
		List<Product> products = new ArrayList<>();
		String sql = "SELECT * from product where is_hot = ? order by pdate desc limit ? ";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, 1);
			pst.setInt(2, 9);
			rs = pst.executeQuery();
			while(rs.next()){
				Product p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
				products.add(p);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return products;
	}

	@Override
	public List<Product> findNewProducts() {
		List<Product> products = new ArrayList<>();
		String sql = "SELECT * from product  order by pdate desc limit ? ";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, 9);
			rs = pst.executeQuery();
			while(rs.next()){
				Product p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
				products.add(p);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return products;
	}

	@Override
	public List<Product> findProductsByCid(String cid) {
		List<Product> products = new ArrayList<>();
		String sql = "SELECT * from product where cid = ?  order by pdate ";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cid);
			rs = pst.executeQuery();
			while(rs.next()){
				Product p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
				products.add(p);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return products;
	}

	@Override
	public Product findProducById(String pid) {
		Product p = null;
		String sql = "SELECT * from product where pid = ?";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, pid);
			rs = pst.executeQuery();
			if(rs.next()){
				p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return p;
	}

	@Override
	public List<Product> findByPage(int page, int size, String cid) {
		List<Product> products = new ArrayList<>();
		String sql = "SELECT * from product where cid = ?  order by pdate limit ?,?";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cid);
			//设置开始查询的位置
			pst.setInt(2, (page-1)*size);
			//设置查询的条数
			pst.setInt(3, size);
			rs = pst.executeQuery();
			while(rs.next()){
				Product p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
				products.add(p);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return products;
	}

	@Override
	public int getTotal(String cid) {
		int count = 0;
		String sql = "select count(*) from product where cid = ?";
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cid);
			rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return count;
	}

	@Override
	public List<Product> findByIds(String[] pids) {
		List<Product> products = new ArrayList<>();
		String sql = "SELECT * from product where pid in (";
		//遍历数组，拼接参数
		for (int i = 0; i < pids.length; i++) {
			String pid = pids[i];
			if(i==pids.length-1){
				sql += "'"+pid+"')";
			}else{
				sql += "'"+pid+"',";
			}
		}
		try {
			con = DataSourceUtils.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				Product p = new Product();
				p.setIs_hot(rs.getInt("is_hot"));
				p.setMarket_price(rs.getDouble("market_price"));
				p.setPdate(rs.getDate("pdate"));
				p.setPdesc(rs.getString("pdesc"));
				p.setPflag(rs.getInt("pflag"));
				p.setPid(rs.getString("pid"));
				p.setPimage(rs.getString("pimage"));
				p.setPname(rs.getString("pname"));
				p.setShop_price(rs.getDouble("shop_price"));
				products.add(p);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DataSourceUtils.closeResultSet(rs);
				DataSourceUtils.closeStatement(pst);
				DataSourceUtils.closeConnection();
			} catch (Exception e2) {
			}
		}
		return products;
	}

}

db.properties

driverClassName=com.mysql.jdbc.Driver
username=root
password=*******
url=jdbc:mysql://localhost:3306/shop
initialSiz=5
maxActive=15

jar包