JDBCUtils:
import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtil { private static DataSource dataSource; //数据库连接池 static { Properties properties = new Properties(); InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties"); try { properties.load(is); is.close(); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (IOException ioException) { ioException.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws Exception { return dataSource.getConnection(); } public static Connection getConnectionOld() throws IOException, ClassNotFoundException, SQLException { InputStream is=JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); is.close(); String driverClass=properties.getProperty("driverClass"); String url=properties.getProperty("url"); String user=properties.getProperty("user"); String password=properties.getProperty("password"); Class.forName(driverClass); Connection connection = DriverManager.getConnection(url, user, password); return connection; } public static void close(Connection connection){ close(connection,null,null); } public static void close(Connection connection, Statement statement){ close(connection,statement,null); } public static void close(Connection connection, Statement statement, ResultSet resultSet){ if(resultSet!=null){ try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(statement!=null){ try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
druid.properties:
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8 username=root password=123456 #初始化容量 initialSize = 3 #最大激活数 maxActive =100 #最小发呆数 minIdle =10
CommonUtil:
import java.io.IOException; import java.sql.*; import java.util.ArrayList; import java.util.List; public class CommonUtil { public static int update(Connection connection,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException { PreparedStatement preparedStatement = null; preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } return preparedStatement.executeUpdate(); } public static void view(Connection connection,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException { List<Customer> list = new ArrayList<>(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; preparedStatement = connection.prepareStatement(sql); //设置参数 for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } resultSet = preparedStatement.executeQuery(); ResultSetMetaData resultSetMetaData =resultSet.getMetaData(); //输出表头 for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) { System.out.print(resultSetMetaData.getColumnLabel(i+1)+"\t"); } System.out.println(); while (resultSet.next()) { for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) { Object object = resultSet.getObject(resultSetMetaData.getColumnLabel(i + 1)); System.out.print(object+"\t"); } System.out.println(); } } }
JDBCDAO:
import com.atguigu.day23.JdbcUtil; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import java.util.List; public class JdbcDao<T> { private QueryRunner queryRunner = new QueryRunner(); private Class<T> clazz; public JdbcDao(Class<T> clazz) { this.clazz = clazz; } public T getBean(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JdbcUtil.getConnection(); BeanHandler<T> beanHandler = new BeanHandler<T>(clazz); return queryRunner.query(connection, sql, beanHandler, args); } finally { JdbcUtil.close(connection); } } public List<T> getList(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JdbcUtil.getConnection(); BeanListHandler<T> beanListHandler = new BeanListHandler<T>(clazz); return queryRunner.query(connection, sql, beanListHandler, args); } finally { JdbcUtil.close(connection); } } public Object getValue(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JdbcUtil.getConnection(); ScalarHandler scalarHandler = new ScalarHandler(); return queryRunner.query(connection, sql, scalarHandler, args); } finally { JdbcUtil.close(connection); } } public int update(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JdbcUtil.getConnection(); return queryRunner.update(connection, sql, args); } finally { JdbcUtil.close(connection); } } }
查询测试:
@Test public void test4(){ JdbcDao<Customer> customerJdbcDao = new JdbcDao<>(Customer.class); try { Customer bean = customerJdbcDao.getBean("select * from customer where id =?",1); System.out.println("bean = " + bean); List<Customer> list = customerJdbcDao.getList("select * from customer where id<?", 4); System.out.println("list = " + list); Object value = customerJdbcDao.getValue("select avg(age) from customer"); System.out.println("value = " + value); } catch (Exception e) { e.printStackTrace(); } }