喜文原创
基于配置文件database.properties用jdbc连接数据库的方法(需要导入的包commons-dbutils-1.6.jar 其所有的增删改查都是基依赖于这个包)
dbutils包方法说明:
ArrayHandler:把结果集中的第一行数据转成对象数组。
ArrayListHandler:把结果集中的每一行数据都转成一个对象数组,再存放到List中。
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。
ColumnListHandler:将结果集中某一列的数据存放到List中。
KeyedHandler:将结果集中的每一行数据都封装到一个Map里,然后再根据指定的key把每个Map再存放到一个Map里。
MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List。
ScalarHandler:将结果集中某一条记录的其中某一列的数据存成Object。
查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
public class QueryRunnerDemo1 { private static Connection con = JDBCUtilsConfig.getConnection(); public static void main(String[] args) throws SQLException{ } public static void mapListHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort"; List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler()); for( Map<String,Object> map : list ){ for(String key : map.keySet()){ System.out.print(key+"..."+map.get(key)); } System.out.println(); } } public static void mapHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort"; Map<String,Object> map = qr.query(con, sql, new MapHandler()); for(String key : map.keySet()){ System.out.println(key+".."+map.get(key)); } } public static void scalarHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET COUNT(*) FROM sort"; long count = qr.query(con, sql, new ScalarHandler<Long>()); System.out.println(count); } public static void columnListHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort "; List<Object> list = qr.query(con, sql, new ColumnListHandler<Object>("sname")); for(Object obj : list){ System.out.println(obj); } } public static void beanListHander()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort "; List<Sort> list = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class)); for(Sort s : list){ System.out.println(s); } } public static void beanHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort "; Sort s = qr.query(con, sql, new BeanHandler<Sort>(Sort.class)); System.out.println(s); } public static void arrayListHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort"; List<Object[]> result= qr.query(con, sql, new ArrayListHandler()); for( Object[] objs : result){ for(Object obj : objs){ System.out.print(obj+" "); } System.out.println(); } } public static void arrayHandler()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "SELET * FROM sort"; Object[] result = qr.query(con, sql, new ArrayHandler()); for(Object obj : result){ System.out.print(obj); } } } |
实现:修改 增加 删除
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
public class QueryRunnerDemo { private static Connection con = JDBCUtilsConfig.getConnection(); public static void main(String[] args)throws SQLException { delete(); } public static void delete()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "DELET FROM sort WHERE sid=?"; int row = qr.update(con, sql, 8); System.out.println(row); DbUtils.closeQuietly(con); } public static void update()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "UPDTE sort SET sname=?,sprice=?,sdesc=? WHERE sid=?"; Object[] params = {"",100.88," ",4}; int row = qr.update(con, sql, params); System.out.println(row); DbUtils.closeQuietly(con); } public static void insert()throws SQLException{ QueryRunner qr = new QueryRunner(); String sql = "INSRT INTO sort (sname,sprice,sdesc)VALUES(?,?,?)"; Object[] params = {"Ʒ",289.32,"Ʒ"}; int row = qr.update(con, sql, params); System.out.println(row); DbUtils.closeQuietly(con); } } |
JDBCUtilsConfig.java文件读取database.properties当中的配置信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.util.Properties; public class JDBCUtilsConfig { private static Connection con ; private static String driverClass; private static String url; private static String username; private static String password; //静态代码块 static{ try{ readConfig(); Class.forName(driverClass); con = DriverManager.getConnection(url, username, password); }catch(Exception ex){ throw new RuntimeException(""); } } private static void readConfig()throws Exception{ InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties"); Properties pro = new Properties(); pro.load(in); driverClass=pro.getProperty("driverClass"); url = pro.getProperty("url"); username = pro.getProperty("username"); password = pro.getProperty("password"); } public static Connection getConnection(){ return con; } } |
database.properties文件配置连接数据库信息
1 2 3 4 5 6 7 |
driverClass=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybase username=root password=123 |
使用自己封装的原生jdbc (JDBCUtils.java)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtils { private JDBCUtils(){} private static Connection con ; static{ try{ Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mybase"; String username="root"; String password="123"; con = DriverManager.getConnection(url, username, password); }catch(Exception ex){ throw new RuntimeException(ex); } } public static Connection getConnection(){ return con; } public static void close(Connection con,Statement stat){ if(stat!=null){ try{ stat.close(); }catch(SQLException ex){} } if(con!=null){ try{ con.close(); }catch(SQLException ex){} } } public static void close(Connection con,Statement stat , ResultSet rs){ if(rs!=null){ try{ rs.close(); }catch(SQLException ex){} } if(stat!=null){ try{ stat.close(); }catch(SQLException ex){} } if(con!=null){ try{ con.close(); }catch(SQLException ex){} } } } |
通过自己封装的JDBCUtils.java获取到数据库的连接信息实现增删改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.domain.Sort; import cn.itcast.jdbcutil.JDBCUtils; public class JDBCDemo { public static void main(String[] args) throws Exception{ Connection con = JDBCUtils.getConnection();//获取数据库连接 PreparedStatement pst = con.prepareStatement("SELET * FROM sort"); ResultSet rs = pst.executeQuery(); List<Sort> list = new ArrayList<Sort>(); while(rs.next()){ Sort s = new Sort(rs.getInt("sid"),rs.getString("sname"), rs.getDouble("sprice"),rs.getString("sdesc")); list.add(s); } JDBCUtils.close(con, pst, rs); for(Sort s : list){ System.out.println(s); } } } |
基于jdbc事务c3p0-config.xml 配置数据库的连接信息
业务层实现的增删改查:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import com.itheima.domain.Category; import com.itheima.domain.Product; import com.itheima.utils.DataSourceUtils; public class AdminProductDao { public List<Product> findAllProduct() throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "selet * from product"; List<Product> productList = runner.query(sql, new BeanListHandler<Product>(Product.class)); return productList; } public List<Category> findAllCategory() throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "selet * from category"; List<Category> categoryList = runner.query(sql, new BeanListHandler<Category>(Category.class)); return categoryList; } public void addProduct(Product product) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "inset into product values(?,?,?,?,?,?,?,?,?,?)"; runner.update(sql, product.getPid(),product.getPname(),product.getMarket_price(), product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(), product.getPdesc(),product.getPflag(),product.getCid()); } public void delProductByPid(String pid) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "delet from product where pid=?"; runner.update(sql, pid); } public Product findProductByPid(String pid) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "selet * from product where pid=?"; Product product = runner.query(sql, new BeanHandler<Product>(Product.class), pid); return product; } public void updateProduct(Product product) throws SQLException { QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "updte product set pname=?,market_price=?,shop_price=?,pimage=?, pdate=?,is_hot=?,pdesc=?,pflag=?,cid=? where pid=?"; runner.update(sql,product.getPname(),product.getMarket_price(), product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(), product.getPdesc(),product.getPflag(),product.getCid(),product.getPid()); } } |
DataSourceUtils.java文件开启事务连接池获取连接数据库信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DataSourceUtils { private static DataSource dataSource = new ComboPooledDataSource(); private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); // 直接可以获取一个连接池 public static DataSource getDataSource() { return dataSource; } public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } // 获取连接对象 public static Connection getCurrentConnection() 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 = getCurrentConnection(); if (con != null) { con.setAutoCommit(false); } } // 事务回滚 public static void rollback() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.rollback(); } } // 提交并且 关闭资源及从ThreadLocall中释放 public static void commitAndRelease() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.commit(); // 事务提交 con.close();// 关闭资源 tl.remove();// 从线程绑定中移除 } } // 关闭资源方法 public static void closeConnection() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.close(); } } 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(); } } } |
c3p0-config.xml配置数据库连接信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
c3p0-config.xml <?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="user">root</property> <property name="password">root</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///web17</property> </default-config> </c3p0-config> |