代码1
import com.mysql.jdbc.Driver; import org.junit.Test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Demo7 { @Test public void test1() throws SQLException { //注册驱动 DriverManager.registerDriver(new Driver()); //建立连接 String url="jdbc:mysql://192.168.177.151:4000/test"; //用户名 String user="root"; //密码 String pwd="123456"; //获取连接 Connection connection=DriverManager.getConnection(url,user,pwd); //创建命令发送器 Statement statement=connection.createStatement(); //准备sql String sql="insert into user values(null,'李白','123')"; //执行sql,获取结果 int i = statement.executeUpdate(sql);//输出1 //输出结果 System.out.println(i); //关闭资源 statement.close(); connection.close(); } @Test public void test2() throws ClassNotFoundException, SQLException { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取连接 String url="jdbc:mysql://192.168.177.151:4000/test"; String user="root"; String passwd="123456"; Connection connection=DriverManager.getConnection(url,user,passwd); //创建命令发送器 Statement statement=connection.createStatement(); //准备sql String sql="update user set passwd='456' where id=1"; //发送sql,获取结果 int i = statement.executeUpdate(sql); System.out.println(i>0?"成功":"失败"); //关闭资源 statement.close(); connection.close(); } }
代码2
sql查询
@Test
public void test2() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://192.168.1.224:3306/test";
String username="root";
String passwd="123456";
Connection connection=DriverManager.getConnection(url,username,passwd);
Statement statement=connection.createStatement();
String sql="select * from user";
ResultSet resultSet=statement.executeQuery(sql);
while (resultSet.next()){
/* int idCol=resultSet.getInt(1);
String name=resultSet.getNString(2);
Object passw=resultSet.getObject(3);
System.out.println(idCol+":"+name+":"+passw);*/
/* int idCol=resultSet.getInt("id");
String name=resultSet.getString("name");
String passw=resultSet.getString("passwd");
System.out.println(idCol+":"+name+":"+passw);*/
Object idCol=resultSet.getObject("id");
Object name=resultSet.getObject("name");
Object passw=resultSet.getObject("passwd");
System.out.println(idCol+":"+name+":"+passw);
}
resultSet.close();
statement.close();
connection.close();
}
代码3
@Test public void test3() throws ClassNotFoundException, SQLException { Scanner scanner=new Scanner(System.in); System.out.println("请输入姓名:"); String name=scanner.next(); System.out.println("请输入密码:"); String passwd=scanner.next(); Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://192.168.1.4:3306/test"; String username="root"; String passwd2="123456"; Connection connection=DriverManager.getConnection(url,username,passwd2); Statement statement=connection.createStatement(); String sql="insert into user values(null,'"+name+"','"+passwd+"')"; int i=statement.executeUpdate(sql); System.out.println(i>0?"成功":"失败"); statement.close(); connection.close(); }
代码4
@Test public void test4() throws ClassNotFoundException, SQLException { Scanner scanner=new Scanner(System.in); System.out.println("input username:"); String sname=scanner.next(); System.out.println("input password:"); String pwd=scanner.next(); User user=judgeUser(sname,pwd); if (user!=null){ System.out.println(user); }else { System.out.println("查无此人"); } } private User judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456"); Statement statement=connection.createStatement(); String sql="select * from user where name='"+sname+"' and passwd = '"+pwd+"'"; ResultSet resultSet=statement.executeQuery(sql); while (resultSet.next()){ int id=resultSet.getInt(1); String name=resultSet.getString(2); String pwd1=resultSet.getString(3); User user=new User(name,pwd1); return user; } resultSet.close(); statement.close(); connection.close(); return null; }
代码5
变量方式插入数据 @Test public void test4() throws ClassNotFoundException, SQLException { Scanner scanner=new Scanner(System.in); System.out.println("input username:"); String sname=scanner.next(); System.out.println("input password:"); String pwd=scanner.next(); judgeUser(sname,pwd); } private void judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456"); Statement statement=connection.createStatement(); String sql="insert into user values (null,?,?)"; PreparedStatement pst =connection.prepareStatement(sql); pst.setObject(1,sname); pst.setObject(2,pwd); int i=pst.executeUpdate(); System.out.println(i>0?"成功":"失败"); pst.close(); statement.close(); connection.close(); }
代码6
变量方式查询 @Test public void test4() throws ClassNotFoundException, SQLException { Scanner scanner=new Scanner(System.in); System.out.println("input username:"); String sname=scanner.next(); System.out.println("input password:"); String pwd=scanner.next(); User user = judgeUser(sname,pwd); if(user!=null){ System.out.println(user); }else { System.out.println("查无此人"); } } private User judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456"); Statement statement=connection.createStatement(); String sql="select * from user where name=? and passwd=?"; PreparedStatement pst =connection.prepareStatement(sql); pst.setObject(1,sname); pst.setObject(2,pwd); ResultSet resultSet=pst.executeQuery(); while (resultSet.next()){ int id=resultSet.getInt(1); String name=resultSet.getString(2); String password=resultSet.getString(3); User user=new User(name,password); return user; } pst.close(); statement.close(); connection.close(); return null; }
代码7
插入图片
@Test public void test5() throws ClassNotFoundException, SQLException, IOException { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456"); String sql="insert into photo values(null,?)"; PreparedStatement preparedStatement=connection.prepareStatement(sql); InputStream ins=new FileInputStream(new File("C:\\Users\\test\\Desktop\\项目.png")); preparedStatement.setBlob(1,ins); int i=preparedStatement.executeUpdate(); System.out.println(i>0?"成功":"失败"); ins.close(); preparedStatement.close(); connection.close(); }
代码8
获取自增id
@Test public void test6() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456"); String sql="insert into user values(null,?,?)"; PreparedStatement preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); preparedStatement.setObject(1,"宝玉"); preparedStatement.setObject(2,"123"); int i=preparedStatement.executeUpdate(); System.out.println(i>0?"成功":"失败"); ResultSet generateKeys=preparedStatement.getGeneratedKeys(); if (generateKeys.next()){ Object object=generateKeys.getObject(1); System.out.println(object); } preparedStatement.close(); connection.close(); }
代码7
批量插入数据
@Test public void test7() throws ClassNotFoundException, SQLException { long startTtime=System.currentTimeMillis(); Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatement","root","123456"); String sql="insert into user values(null,?,?)"; PreparedStatement preparedStatement=connection.prepareStatement(sql); for (int i = 0; i <1000 ; i++) { preparedStatement.setObject(1,"小梦"+i); preparedStatement.setObject(2,"123"+i); preparedStatement.addBatch(); } preparedStatement.executeBatch(); long endTime=System.currentTimeMillis(); System.out.println("耗时:"+(endTime-startTtime)); preparedStatement.close(); connection.close(); }
代码8
事务
@Test public void test8(){ try { Class.forName("com.mysql.jdbc.Driver"); Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.14:3306/test?rewriteBatchedStatement","root","123456"); String sql1="update account set balance=balance-500 where name='李白'"; String sql2="update account set balance=balance-500 where name='杜甫'"; PreparedStatement preparedStatement1=connection.prepareStatement(sql1); PreparedStatement preparedStatement2=connection.prepareStatement(sql2); preparedStatement1.executeUpdate(); preparedStatement2.executeUpdate(); System.out.println("转账成功"); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } }
代码9
开始事务,关闭自动提交
@Test public void test8() { Connection connection=null; PreparedStatement preparedStatement1=null; PreparedStatement preparedStatement2=null; try { Class.forName("com.mysql.jdbc.Driver"); connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatement","root","123456"); connection.setAutoCommit(false); String sql1="update account set balance=balance-500 where name='李白'"; String sql2="update account set balance=balance-500 where name='杜甫'"; preparedStatement1=connection.prepareStatement(sql1); preparedStatement2=connection.prepareStatement(sql2); preparedStatement1.executeUpdate(); preparedStatement2.executeUpdate(); connection.commit(); System.out.println("转账成功"); } catch (ClassNotFoundException | SQLException e) { try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } System.out.println(e.getMessage()); }finally { if (preparedStatement1!=null){ try { preparedStatement1.close(); } catch (SQLException e) { e.printStackTrace(); } } if (preparedStatement2!=null){ try { preparedStatement2.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
代码10
工具类
package day19.utils; import java.sql.*; public class JDBCUtils { //获取连接 public static Connection getConnection() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://192.168.1.4:3306/test"; String user="root"; String pwd="123456"; Connection connection= DriverManager.getConnection(url,user,pwd); return connection; } //关闭资源 public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (connection!=null){ connection.close(); } if (statement!=null){ statement.close(); } if (resultSet!=null){ resultSet.close(); } } } 之前的事务代码可以改成: @Test public void test8() { Connection connection=null; PreparedStatement preparedStatement1=null; PreparedStatement preparedStatement2=null; try { Class.forName("com.mysql.jdbc.Driver"); connection=JDBCUtils.getConnection(); connection.setAutoCommit(false); String sql1="update account set balance=balance-500 where name='李白'"; String sql2="update account set balance=balance+500 where name='杜甫'"; preparedStatement1=connection.prepareStatement(sql1); preparedStatement2=connection.prepareStatement(sql2); preparedStatement1.executeUpdate(); preparedStatement2.executeUpdate(); connection.commit(); System.out.println("转账成功"); } catch (ClassNotFoundException | SQLException e) { try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } System.out.println(e.getMessage()); }finally { try { JDBCUtils.closeResources(connection,preparedStatement1,null); JDBCUtils.closeResources(null,preparedStatement2,null); } catch (SQLException e) { e.printStackTrace(); } } }
代码11.
properties使用
import org.junit.Test; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.Properties; public class PropertiesTest { @Test public void test01(){ //获取系统的配置信息 Properties properties=System.getProperties(); //输出所有的配置信息 properties.list(System.out); //输出具体配置信息 String codeValue=properties.getProperty("file.encoding"); System.out.println(codeValue);//UTF-8 } @Test public void test02() throws IOException { Properties properties=new Properties(); properties.load(new FileInputStream(new File("C:\\Users\\test\\Downloads\\abc.properties"))); System.out.println(properties);//{user=root, password=123456} } }
代码12
通过properties优化工具类
jdbc.properties user=root password=123456 url=jdbc:mysql://192.168.1.4:3306/test driver=com.mysql.jdbc.Driver ------------------------- JDBCUtils.java import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.*; import java.util.Properties; public class JDBCUtils { public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException { Connection connection= getConnection(); System.out.println(connection); } //获取连接 static String user; static String password; static String url; static { Properties properties=new Properties(); try { //properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties")); properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\jdbc.properties"))); user=properties.getProperty("user"); url=properties.getProperty("url"); password=properties.getProperty("password"); String driver=properties.getProperty("driver"); Class.forName(driver); } catch (IOException e) { e.printStackTrace(); }catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException { Connection connection=DriverManager.getConnection(url,user,password); return connection; } //关闭资源 public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (connection!=null){ connection.close(); } if (statement!=null){ statement.close(); } if (resultSet!=null){ resultSet.close(); } } } -------------------------- @Test public void test8() { Connection connection=null; PreparedStatement preparedStatement1=null; PreparedStatement preparedStatement2=null; try { connection=JDBCUtils.getConnection(); connection.setAutoCommit(false); String sql1="update account set balance=balance-500 where name='李白'"; String sql2="update account set balance=balance+500 where name='杜甫'"; preparedStatement1=connection.prepareStatement(sql1); preparedStatement2=connection.prepareStatement(sql2); preparedStatement1.executeUpdate(); preparedStatement2.executeUpdate(); connection.commit(); System.out.println("转账成功"); } catch (ClassNotFoundException | SQLException | IOException e) { try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } System.out.println(e.getMessage()); }finally { try { JDBCUtils.closeResources(connection,preparedStatement1,null); JDBCUtils.closeResources(null,preparedStatement2,null); } catch (SQLException e) { e.printStackTrace(); } } }
代码13
druid示例
https://repo1.maven.org/maven2/com/alibaba/druid/1.0.10/druid-1.0.10.jar 把druid-1.0.10.jar包放到lib目录下 @Test public void test9() throws SQLException { //创建对象 DruidDataSource druidDataSource=new DruidDataSource(); //基本配置 druidDataSource.setUsername("root"); druidDataSource.setPassword("123456"); druidDataSource.setUrl("jdbc:mysql://192.168.1.4:3306/test"); //连接池配置 druidDataSource.setInitialSize(5);//连接池初始化5条连接 druidDataSource.setMaxActive(10);//最多可以有几个连接 druidDataSource.setMaxWait(2000);//最多等待时间 //获取连接 for (int i = 0; i < 20; i++) { DruidPooledConnection connection=druidDataSource.getConnection(); System.out.println(connection); connection.close(); } } //输出 三月 18, 2022 11:30:45 上午 com.alibaba.druid.pool.DruidDataSource info 信息: {dataSource-1} inited com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3 com.mysql.jdbc.JDBC4Connection@4590c9c3
代码14
druid连接池
@Test public void test9() throws SQLException { //创建对象 DruidDataSource druidDataSource=new DruidDataSource(); //基本配置 druidDataSource.setUsername("root"); druidDataSource.setPassword("123456"); druidDataSource.setUrl("jdbc:mysql://192.168.1.4:3306/test"); //连接池配置 druidDataSource.setInitialSize(5);//连接池初始化5条连接 druidDataSource.setMaxActive(10);//最多可以有几个连接 druidDataSource.setMaxWait(2000);//最多等待时间 //获取连接 for (int i = 0; i < 20; i++) { DruidPooledConnection connection=druidDataSource.getConnection(); System.out.println(connection); connection.close(); } }
代码15
完整druid工具代码
druid.properties ---------------------------------------------- driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatements=true username=root password=123456 initialSize=5 maxActive=10 maxWait=1000 JDBCUtils2.class ------------------------------------------- import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtils2 { public static void main(String[] args) throws SQLException { Connection connection=getConnection(); System.out.println(connection); } static DataSource dataSource; static { Properties properties=new Properties(); try { // properties.load(JDBCUtils2.class.getClassLoader().getResourceAsStream("druid.properties")); properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\druid.properties"))); dataSource=DruidDataSourceFactory.createDataSource(properties); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { Connection connection=dataSource.getConnection(); return connection; } //关闭资源 public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (resultSet!=null){ resultSet.close(); } if (statement!=null){ resultSet.close(); } if (connection!=null){ connection.close(); } } } BaseDao.class ----------------------------------------- import day19.utils.JDBCUtils2; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class BaseDao { public int commonUpdate(String sql,Object...objs) throws SQLException { //获取连接 Connection connection= JDBCUtils2.getConnection(); //准备sql,准备命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); if (objs!=null && objs.length>0){ for (int i = 0; i <objs.length ; i++) { preparedStatement.setObject(i+1,objs[i]); } } //获取执行命令结果 int i=preparedStatement.executeUpdate(); return i; } } 测试代码 ------------------------------------ @Test public void test10() throws SQLException { BaseDao baseDao=new BaseDao(); String sql="insert into user values(null,?,?)"; Object[] o={"李小白","123456"}; int i=baseDao.commonUpdate(sql,o); System.out.println(i); }
代码16
整合后的查询
package day19; public class Account { private int id; private String sname; private int balance; public Account() { } public Account(int id, String sname, int balance) { this.id = id; this.sname = sname; this.balance = balance; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public int getBalance() { return balance; } public void setBalance(int balance) { this.balance = balance; } @Override public String toString() { return "Account{" + "id=" + id + ", sname='" + sname + '\'' + ", balance=" + balance + '}'; } }
@Test public void test1() throws SQLException { //注册驱动,获取连接 Connection connection= JDBCUtils2.getConnection(); //准备sql String sql="select * from account"; //创建命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); ResultSet resultSet=preparedStatement.executeQuery(); //处理结果 while (resultSet.next()){ Object o1=resultSet.getObject(1); Object o2=resultSet.getObject(2); Object o3=resultSet.getObject(3); System.out.println(o1+"-"+o2+"-"+o3); } JDBCUtils2.closeResources(connection,preparedStatement,resultSet); }
代码17
获取表头和字段
@Test public void test1() throws SQLException { //注册驱动,获取连接 Connection connection= JDBCUtils2.getConnection(); //准备sql String sql="select * from account"; //创建命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); //获取表头的数据 ResultSetMetaData metaData=preparedStatement.getMetaData(); //获取字段数 int count=metaData.getColumnCount(); System.out.println("字段数:"+count); for (int i = 0; i <count ; i++) { System.out.println(metaData.getColumnName(i+1));//输出字段名 } //执行命令获取结果 ResultSet resultSet=preparedStatement.executeQuery(); //处理结果 while (resultSet.next()){ Object o1=resultSet.getObject(1); Object o2=resultSet.getObject(2); Object o3=resultSet.getObject(3); System.out.println(o1+"-"+o2+"-"+o3); } JDBCUtils2.closeResources(connection,preparedStatement,resultSet); }
代码18
获取字段别名,表名,字段类型
@Test public void test1() throws SQLException { //注册驱动,获取连接 Connection connection= JDBCUtils2.getConnection(); //准备sql String sql="select id,name sname,balance balances from account"; //创建命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); //获取表头的数据 ResultSetMetaData metaData=preparedStatement.getMetaData(); //获取字段数 int count=metaData.getColumnCount(); System.out.println("字段数:"+count); for (int i = 0; i <count ; i++) { System.out.println(metaData.getColumnName(i+1));//输出字段名 System.out.println(metaData.getColumnLabel(i+1));//输出字段别名 System.out.println(metaData.getColumnClassName(i+1));//输出字段类型 System.out.println(metaData.getTableName(i+1));//输出表名 System.out.println("------------------------"); } //执行命令获取结果 ResultSet resultSet=preparedStatement.executeQuery(); //处理结果 while (resultSet.next()){ Object o1=resultSet.getObject(1); Object o2=resultSet.getObject(2); Object o3=resultSet.getObject(3); System.out.println(o1+"-"+o2+"-"+o3); } JDBCUtils2.closeResources(connection,preparedStatement,resultSet); }
代码19
通用增删改查代码
import day19.utils.JDBCUtils2; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class BaseSelectDao { public int update(String sql,Object...args) throws SQLException { //获取连接 Connection connection= JDBCUtils2.getConnection(); PreparedStatement preparedStatement=connection.prepareStatement(sql); if (args!=null && args.length>0){ for (int i = 0; i <args.length ; i++) { preparedStatement.setObject(i+1,args[i]); } } int i = preparedStatement.executeUpdate(); return i; } }
@Test public void test2() throws SQLException { BaseSelectDao baseSelectDao=new BaseSelectDao(); String sql="insert into account values (null,?,?)"; Object[] objects={"杜小甫",1000}; int i =baseSelectDao.update(sql,objects); System.out.println(i); }
代码20
通用查询
import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.List; public class BaseAll { public int update(String sql,Object...args){ return 1; } /* * 获取查询的数据封装为集合 * @param clazz对应类型的class对象 * @param sql要执行的sql * @param args要传递的参数 * @param <T> 具体的类型 * @return * */ public <T> List<T> getAll(Class<T> clazz, String sql, Object...args) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException { //创建存储所有数据的集合 List<T> list=new ArrayList<>(); //获取连接 Connection connection=JDBCUtils2.getConnection(); //准备命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); //获取元数据 ResultSetMetaData metaData=preparedStatement.getMetaData(); //获取列 int count = metaData.getColumnCount(); //设置值 if (args!=null && args.length>0){ for (int i = 0; i <args.length ; i++) { preparedStatement.setObject(i+1,args[i]); } } //执行命令,获取结果集 ResultSet resultSet=preparedStatement.executeQuery(); while (resultSet.next()){ //使用反射创建对象 T instance=clazz.newInstance(); //获取字段名再获取对应的值 for (int i = 0; i <count ; i++) { //获取字段名 String columnlabel=metaData.getColumnLabel(i+1); //获取对应的值 Object object=resultSet.getObject(columnlabel); //获取字段对应的属性 Field field=clazz.getDeclaredField(columnlabel); field.setAccessible(true); //将值变为对象的属性值 field.set(instance,object); } //将对象加到集合内 list.add(instance); } return list; } }
import com.mysql.jdbc.Driver; import org.junit.Test; import com.atguigu.day19.Account; import java.sql.*; import java.util.List; import java.util.Properties; import java.util.logging.Logger; public class Demo1 { @Test public void test1() throws SQLException, IllegalAccessException, NoSuchFieldException, InstantiationException { BaseAll baseAll=new BaseAll(); String sql="select id,name sname,balance from account"; List<Account> all= baseAll.getAll(Account.class,sql,null); all.forEach(System.out::println); } }
代码21
@Test public void test2() throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException { BaseAll baseAll=new BaseAll(); String sql="select * from user"; List<User> all=baseAll.getAll(User.class,sql,null); all.forEach(System.out::println); } @Test public void test3() throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException { BaseAll baseAll=new BaseAll(); String sql="select * from user where id>? and passwd=?"; Object[] arr={3,"123"}; List<User> all=baseAll.getAll(User.class,sql,arr); all.forEach(System.out::println); }
代码22
获取单个值
import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.List; public class BaseAll { public int update(String sql, Object... args) { return 1; } /* * 获取查询的数据封装为集合 * @param clazz对应类型的class对象 * @param sql要执行的sql * @param args要传递的参数 * @param <T> 具体的类型 * @return * */ public <T> List<T> getAll(Class<T> clazz, String sql, Object... args) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException { //创建存储所有数据的集合 List<T> list = new ArrayList<>(); //获取连接 Connection connection = JDBCUtils2.getConnection(); //准备命令发送器 PreparedStatement preparedStatement = connection.prepareStatement(sql); //获取元数据 ResultSetMetaData metaData = preparedStatement.getMetaData(); //获取列 int count = metaData.getColumnCount(); //设置值 if (args != null && args.length > 0) { for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i + 1, args[i]); } } //执行命令,获取结果集 ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { //使用反射创建对象 T instance = clazz.newInstance(); //获取字段名再获取对应的值 for (int i = 0; i < count; i++) { //获取字段名 String columnlabel = metaData.getColumnLabel(i + 1); //获取对应的值 Object object = resultSet.getObject(columnlabel); //获取字段对应的属性 Field field = clazz.getDeclaredField(columnlabel); field.setAccessible(true); //将值变为对象的属性值 field.set(instance, object); } //将对象加到集合内 list.add(instance); } return list; } //获取单个值 public <T> T getOne(Class<T> clazz, String sql, Object... args) throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException { List<T> all = getAll(clazz, sql, args); T t = all.get(0); return t; } } ------------------------------------------ @Test public void test4() throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException { BaseAll baseOne=new BaseAll(); String sql="select * from user where id=?"; User one=baseOne.getOne(User.class,sql,5); System.out.println(one); }
代码23
//通用的没有实体类对应的编码 import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class BaseAll { /** * 通用的没有实体类对应的编码 * @param sql * @param args * @return */ public List<Map<String,Object>> mapToList(String sql,Object...args) throws SQLException { //创建集合 List<Map<String,Object>> list=new ArrayList<>(); //获取连接 Connection connection=JDBCUtils2.getConnection(); //创建命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); //设置值 if (args!=null && args.length>0){ for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } } //获取元数据 ResultSetMetaData metaData = preparedStatement.getMetaData(); //获取一共多少字段 int columnCount = metaData.getColumnCount(); //执行命令获取结果 ResultSet resultSet = preparedStatement.executeQuery(); //遍历结果 while (resultSet.next()){ //创建map,存储值 Map<String,Object> map=new HashMap<>(); for (int i = 0; i <columnCount ; i++) { //获取字段名 String columnLabel = metaData.getColumnLabel(i+1); //获取字段值 Object object = resultSet.getObject(columnLabel); map.put(columnLabel,object); } //将map添加到集合内 list.add(map); } //关闭资源 JDBCUtils2.closeResources(connection,preparedStatement,resultSet); return list; } }
@Test public void test5() throws SQLException { BaseAll baseAll=new BaseAll(); String sql="select uid,avg(balance) avg_balance,max(balance) max_balance from account2 where uid>? group by uid"; List<Map<String,Object>> list=baseAll.mapToList(sql,180438); System.out.println(list); list.forEach(System.out::println); }
代码24
未使用ThreadLocal完成事务控制
@Test public void test3(){ String sql1="update account set balance=balance-500 where id=1"; String sql2="update account set balance=balance+500 where id=2"; BaseDao baseDao=new BaseDao(); Connection connection=null; try { connection=JDBCUtils2.getConnection(); System.out.println("连接:"+connection+",线程:"+ Thread.currentThread().getName()); connection.setAutoCommit(false); baseDao.commonUpdate(sql1,null); baseDao.commonUpdate(sql2,null); connection.commit(); } catch (SQLException e) { e.printStackTrace(); try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } }
代码25
使用ThreadLocal完成事务控制
JDBCUtils2 -------------------------------------- import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtils2 { public static void main(String[] args) throws SQLException { Connection connection=getConnection(); System.out.println(connection); } static DataSource dataSource; static ThreadLocal<Connection> threadLocal=new ThreadLocal<>(); static { Properties properties=new Properties(); try { // properties.load(JDBCUtils2.class.getClassLoader().getResourceAsStream("druid.properties")); properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\druid.properties"))); dataSource=DruidDataSourceFactory.createDataSource(properties); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { Connection connection=threadLocal.get(); if (connection==null){ connection=dataSource.getConnection(); threadLocal.set(connection); } return connection; } //关闭资源 public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (resultSet!=null){ resultSet.close(); threadLocal.remove(); } if (statement!=null){ resultSet.close(); } if (connection!=null){ connection.close(); } } } ----------------------------------------------- /** * 使用ThreadLocal完成事务控制 */ @Test public void test3(){ String sql1="update account set balance=balance-500 where id=1"; String sql2="update account set balance=balance+500 where id=2"; BaseDao baseDao=new BaseDao(); Connection connection=null; try { connection=JDBCUtils2.getConnection(); System.out.println("连接:"+connection+",线程:"+ Thread.currentThread().getName()); connection.setAutoCommit(false); baseDao.commonUpdate(sql1,null); baseDao.commonUpdate(sql2,null); connection.commit(); } catch (SQLException e) { e.printStackTrace(); try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } }
代码26
通用获取一个值的方法
BaseDao -------------------- import day19.utils.JDBCUtils2; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class BaseDao { public int commonUpdate(String sql,Object...objs) throws SQLException { //获取连接 Connection connection= JDBCUtils2.getConnection(); //准备sql,准备命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); if (objs!=null && objs.length>0){ for (int i = 0; i <objs.length ; i++) { preparedStatement.setObject(i+1,objs[i]); } } //获取执行命令结果 int i=preparedStatement.executeUpdate(); return i; } /** * 通用获取一个值的方法 * @param sql * @param args * @return */ public Object getSingleValue(String sql,Object...args) throws SQLException { //获取连接 Connection connection=JDBCUtils2.getConnection(); //创建命令发送器 PreparedStatement preparedStatement=connection.prepareStatement(sql); //设置值 if (args!=null && args.length>0){ for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } } ResultSet resultSet = preparedStatement.executeQuery(); if (resultSet.next()){ Object object=resultSet.getObject(1); return object; } return null; } } ----------------------- @Test public void test4() throws SQLException { BaseDao baseDao = new BaseDao(); String sql="select count(*) from account"; Object singleValue=baseDao.getSingleValue(sql,null); System.out.println(singleValue); }
代码27
common-dbutils
package day19; import day19.utils.JDBCUtils2; 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.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import javax.management.Query; import java.sql.Connection; import java.sql.SQLException; import java.util.List; import java.util.Map; public class BaseDao2 { QueryRunner queryRunner=new QueryRunner(); /** *通用的增删改 * @param sql * @param args * @return * @throws SQLException */ public int update(String sql,Object...args) throws SQLException { Connection connection= JDBCUtils2.getConnection(); return queryRunner.update(connection,sql,args); } public <T> List<T> getAll(Class<T> clazz,String sql,Object...args) throws SQLException { Connection connection=JDBCUtils2.getConnection(); return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args); } public <T> T getOne(Class<T> clazz,String sql,Object...args) throws SQLException { return queryRunner.query(JDBCUtils2.getConnection(),sql,new BeanHandler<>(clazz),args); } public List<Map<String,Object>> mapTolist(String sql,Object...args) throws SQLException { return queryRunner.query(JDBCUtils2.getConnection(),sql,new MapListHandler(),args); } public Object getSingle(String sql,Object...args) throws SQLException { return queryRunner.query(JDBCUtils2.getConnection(),sql,new ScalarHandler<>(),args); } }
//update @Test public void test5() throws SQLException { BaseDao2 baseDao2=new BaseDao2(); String sql="update user set name='杜小甫' where id=?"; Object[] arr={1}; int i=baseDao2.update(sql,arr); System.out.println(i>0?"成功":"失败"); } //getall @Test public void test6() throws SQLException { BaseDao2 baseDao2 = new BaseDao2(); String sql="select id,name sname,balance from account where id between ? and ?"; Object[] arr={1,5}; List<Account> all=baseDao2.getAll(Account.class,sql,arr); all.forEach(System.out::println); } //getone @Test public void test7() throws SQLException { BaseDao2 baseDao2 = new BaseDao2(); String sql="select id,name sname, passwd pwd from user where id=?"; Object[] arr={5}; User one = baseDao2.getOne(User.class, sql, arr); System.out.println(one); } //mapTolist @Test public void test8() throws SQLException { BaseDao2 baseDao2 = new BaseDao2(); String sql="select * from account where id between ? and ?"; Object[] arr={1,5}; List<Map<String, Object>> maps = baseDao2.mapTolist(sql,arr); for (Map<String, Object> map : maps) { System.out.println(map); } } //getSingle @Test public void test9() throws SQLException { BaseDao2 baseDao2 = new BaseDao2(); String sql="select name from user where id=?"; Object[] arr={6}; Object single = baseDao2.getSingle(sql, arr); System.out.println(single); }