devbox
小蓝xlanll
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Java—jdbc数据库连接以及增删改查操作_java实现对数据库一个表的增删改查

作者：小蓝xlanll | 2024-05-15 23:17:37

赞

踩

java实现对数据库一个表的增删改查

 1、需要先下载jdbc jar包，然后添加到工程上面 

  1. package jdbctest;
  2.  
  3. import org.junit.Test;
  4.  
  5. import java.io.InputStream;
  6. import java.sql.Connection;
  7. import java.sql.Driver;
  8. import java.sql.DriverManager;
  9. import java.sql.SQLException;
  10. import java.util.Properties;
  11.  
  12. public class ConnectTest {
  13.         @Test
  14.     public void coonect4() throws Exception {
  15.         // 最终版，将账号信息进行配置化，先在模块下的src下建立配置文件,读取文件
  16.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
  17.         Properties pros = new Properties();
  18.         pros.load(is);
  19.         String url = pros.getProperty("url");
  20.         String user = pros.getProperty("user");
  21.         String password = pros.getProperty("password");
  22.         String driver = pros.getProperty("driver");
  23.         //加载驱动
  24.         Class.forName(driver);
  25.         Connection conn =  DriverManager.getConnection(url,user,password);
  26.         System.out.println(conn);
  27.     }
  28. }

 配置文件 

 

PreparedStatement相比Statement的好处

1. PreparedStatement操作Blob的数据，而Statement做不到。
2. PreparedStatement可以实现更高效的批量操作。

3.解决Statement的拼串、sql问题
 

对数据库的增删改

  1. package com.ruqi.prepareStatment;
  2. import org.junit.Test;
  3. import java.io.InputStream;
  4. import java.sql.*;
  5. import java.text.SimpleDateFormat;
  6. import java.util.Properties;
  7.  
  8. public class PrepareStatmentTest {
  9.     // 通用的增删改
  10.     public void updateCommonUpdate(String sql, Object ...args) {
  11.         Connection conn = null;
  12.         PreparedStatement ps = null;
  13.         try {
  14.             conn = PrepareStatmentTest.getConnection();
  15.             ps = conn.prepareStatement(sql);
  16.             for (int i = 0; i < args.length; i++) {
  17.                 ps.setObject(i + 1, args[i]);
  18.             }
  19.             ps.execute(); // 查询sql返回true，其他返回false
  20.             ps.executeUpdate(); // 返回更新成功的条数
  21.         }catch (Exception e){
  22.             e.printStackTrace();
  23.         }finally {
  24.             PrepareStatmentTest.closeConnection(conn,ps);
  25.         }
  26.     }
  27.  
  28.     @Test
  29.     public void updatesql(){
  30.         String sql = "delete from Scores where id = ?;";
  31.         updateCommonUpdate(sql,8);
  32.     }
  33.     
  34.  
  35.     // 对数据进行增删改操作
  36.     @Test
  37.     public void prepareStatmentUpdate() throws Exception {
  38.         Connection conn = null;
  39.         conn = PrepareStatmentTest.getConnection();
  40.         // 预编译sql，创建preparesstament对象
  41.         String sql = "insert into Scores(id,score,date)values(?,?,?)"; //?表示点位符
  42.         PreparedStatement ps = conn.prepareStatement(sql);
  43.        // 填充点位符
  44.         ps.setInt(1,8); // 下标按从1开始计算，并且每个数据要指定相应类型
  45.         ps.setDouble(2,9.3);
  46.         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  47.         java.util.Date date = sdf.parse("2022-01-01");
  48.         ps.setDate(3, new Date(date.getTime()));
  49.         // 执行sql
  50.         ps.execute();
  51.         PrepareStatmentTest.closeConnection(conn,ps);
  52.     }
  53.  
  54.  
  55.     public static Connection getConnection() throws Exception {
  56.         // 获取链接
  57.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
  58.         Properties pros = new Properties();
  59.         pros.load(is);
  60.         String url = pros.getProperty("url");
  61.         String user = pros.getProperty("user");
  62.         String password = pros.getProperty("password");
  63.         String driver = pros.getProperty("driver");
  64.         //加载驱动
  65.         Class.forName(driver);
  66.         //获取连接
  67.         Connection conn =  DriverManager.getConnection(url,user,password);
  68.         return conn;
  69.     }
  70.  
  71.     public static void closeConnection(Connection conn, PreparedStatement ps){
  72.         if(ps!=null){
  73.             try {
  74.                 ps.close();
  75.             }catch (Exception e){
  76.                 e.printStackTrace();
  77.             }
  78.         }
  79.         if(conn!=null){
  80.             try {
  81.                 conn.close();
  82.             }catch (Exception e){
  83.                 e.printStackTrace();
  84.             }
  85.         }
  86.     }
  87. }

对数据库进行查询操作

  1. package com.ruqi.prepareStatment;
  2. import org.junit.Test;
  3. import java.io.InputStream;
  4. import java.lang.reflect.Field;
  5. import java.net.CookieHandler;
  6. import java.sql.*;
  7. import java.util.Properties;
  8.  
  9. public class SelectSQL {
  10.  
  11.     @Test
  12.     public void queryScore() {
  13.         String sql = "select score from Scores where id = ?;";
  14.         ScoresObject score = scoreCommonSelect(sql, 2);
  15.         System.out.println(score);
  16.     }
  17.  
  18.     // 针对不同表多行数据的通用查询
  19.     public <T> List<T> commonManySelect(Class<T> clazz, String sql, Object... args) {
  20.         Connection conn = null;
  21.         PreparedStatement ps = null;
  22.         ResultSet rs = null;
  23.         try {
  24.             conn = ConnectUtil.getConnection();
  25.             ps = conn.prepareStatement(sql);
  26.             for (int i = 0; i < args.length; i++) {
  27.                 ps.setObject(i + 1, args[i]);
  28.             }
  29.             rs = ps.executeQuery();
  30.             ResultSetMetaData rsmeta = rs.getMetaData(); 
  31.             ArrayList<T> list = new ArrayList<T>();
  32.             while (rs.next()) {
  33.                 T t = clazz.newInstance();
  34.                 for (int i = 0; i < rsmeta.getColumnCount(); i++) {
  35.                     Object columnValue = rs.getObject(i + 1);
  36.                     String columnLabel = rsmeta.getColumnLabel(i + 1);
  37.                     Field decColumnName = clazz.getDeclaredField(columnLabel);
  38.                     decColumnName.setAccessible(true);
  39.                     decColumnName.set(t, columnValue);
  40.                 }
  41.                 list.add(t);
  42.             }
  43.             return list;
  44.         } catch (Exception e) {
  45.             e.printStackTrace();
  46.         } finally {
  47.             ConnectUtil.closeConnection(conn, ps, rs);
  48.         }
  49.         return null;
  50.     }
  51.  
  52.  
  53. // 针对不同表一行数据的通用查询
  54.     public <T> T commonSelect(Class<T> clazz, String sql, Object... args) {
  55.         Connection conn = null;
  56.         PreparedStatement ps = null;
  57.         ResultSet rs = null;
  58.         try {
  59.             conn = ConnectUtil.getConnection();
  60.             ps = conn.prepareStatement(sql);
  61.             for (int i = 0; i < args.length; i++) {
  62.                 ps.setObject(i + 1, args[i]);
  63.             }
  64.             rs = ps.executeQuery();
  65.             ResultSetMetaData rsmeta = rs.getMetaData(); //获取元数据，通过这个对象获取数据的个数
  66.             if (rs.next()) {
  67.                 T t = clazz.newInstance();
  68.                 for (int i = 0; i < rsmeta.getColumnCount(); i++) {
  69.                     // 获取列值
  70.                     Object columnValue = rs.getObject(i + 1);
  71.                     // 获取列名，不推荐使用
  72.                     // String columnName = rsmeta.getColumnName(i + 1);
  73.                     // 获取别名，为了解决对象属性与数据表的列名不完全一致的情况，
  74.                     // 解决方法是：写sql时，对每个字段取别名，别名要跟对象属性一致，就可以解决上述问题
  75.                     String columnLabel = rsmeta.getColumnLabel(i + 1);
  76.                     Field decColumnName = clazz.getDeclaredField(columnLabel);
  77.                     decColumnName.setAccessible(true);
  78.                     decColumnName.set(t, columnValue);
  79.                 }
  80.                 return t;
  81.             }
  82.         } catch (Exception e) {
  83.             e.printStackTrace();
  84.         } finally {
  85.             ConnectUtil.closeConnection(conn, ps, rs);
  86.         }
  87.         return null;
  88.     }
  89.  
  90.  
  91.     // 针对一个表一行数据的通用查询
  92.     public ScoresObject scoreCommonSelect(String sql, Object... args) {
  93.         Connection conn = null;
  94.         PreparedStatement ps = null;
  95.         ResultSet rs = null;
  96.         try {
  97.             conn = ConnectUtil.getConnection();
  98.             ps = conn.prepareStatement(sql);
  99.             for (int i = 0; i < args.length; i++) {
  100.                 ps.setObject(i + 1, args[i]);
  101.             }
  102.             rs = ps.executeQuery();
  103.             ResultSetMetaData rsmeta = rs.getMetaData(); //获取元数据，通过这个对象获取数据的个数
  104.             if (rs.next()) {
  105.                 ScoresObject soc = new ScoresObject();
  106.                 for (int i = 0; i < rsmeta.getColumnCount(); i++) {
  107.                     // 获取列值
  108.                     Object columnValue = rs.getObject(i + 1);
  109.                      // 获取列名，不推荐使用
  110.                     // String columnName = rsmeta.getColumnName(i + 1);
  111.                     // 获取别名，为了解决对象属性与数据表的列名不完全一致的情况，
  112.                     // 解决方法是：写sql时，对每个字段取别名，别名要跟对象属性一致，就可以解决上述问题
  113.                     String columnLabel = rsmeta.getColumnLabel(i + 1);
  114.                     Field decColumnName = ScoresObject.class.getDeclaredField(columnLabel);
  115.                     decColumnName.setAccessible(true);
  116.                     decColumnName.set(soc, columnValue);
  117.                 }
  118.                 return soc;
  119.             }
  120.         } catch (Exception e) {
  121.             e.printStackTrace();
  122.         } finally {
  123.             ConnectUtil.closeConnection(conn, ps, rs);
  124.         }
  125.         return null;
  126.     }
  127.  
  128.     @Test
  129.     public void selectSql() {
  130.         Connection conn = null;
  131.         PreparedStatement ps = null;
  132.         ResultSet rs = null;
  133.         try {
  134.             conn = ConnectUtil.getConnection();
  135.             String sql = "select * from Scores where id = ?;";
  136.             ps = conn.prepareStatement(sql);
  137.             ps.setObject(1,2);
  138.             // 获取结果集
  139.             rs = ps.executeQuery();
  140.             //处理结果集
  141.             if(rs.next()){//判断是否有数据，如果有,指针下移并返回true
  142.                 //获取结果集的各个数据
  143.                 int id = rs.getInt(1);
  144.                 double score = rs.getInt(2);
  145.                 Date date = rs.getDate(3);
  146.                 //ORM思想：对象关系映射，将每个结果集都放到一个对象，每个对象表示一行数据，对象的数据表示每列的值
  147.                 ScoresObject result = new ScoresObject(id, score, date);
  148.                 System.out.println(result);
  149.             }
  150.         }catch (Exception e){
  151.             e.printStackTrace();
  152.         }finally {
  153.             ConnectUtil.closeConnection(conn,ps,rs);
  154.         }
  155.  
  156.     }
  157.  
  158. }
  159.  
  160.  
  161. ==================================================
  162.  
  163. package com.ruqi.prepareStatment;
  164.  
  165. import java.io.InputStream;
  166. import java.sql.Connection;
  167. import java.sql.DriverManager;
  168. import java.sql.PreparedStatement;
  169. import java.sql.ResultSet;
  170. import java.util.Properties;
  171.  
  172. public class ConnectUtil {
  173.  
  174.     public static Connection getConnection() throws Exception {
  175.         // 获取链接
  176.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
  177.         Properties pros = new Properties();
  178.         pros.load(is);
  179.         String url = pros.getProperty("url");
  180.         String user = pros.getProperty("user");
  181.         String password = pros.getProperty("password");
  182.         String driver = pros.getProperty("driver");
  183.         //加载驱动
  184.         Class.forName(driver);
  185.         //获取连接
  186.         Connection conn =  DriverManager.getConnection(url,user,password);
  187.         return conn;
  188.     }
  189.  
  190.     public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs){
  191.         if(ps!=null){
  192.             try {
  193.                 ps.close();
  194.             }catch (Exception e){
  195.                 e.printStackTrace();
  196.             }
  197.         }
  198.         if(conn!=null){
  199.             try {
  200.                 conn.close();
  201.             }catch (Exception e){
  202.                 e.printStackTrace();
  203.             }
  204.         }if(rs!=null){
  205.             try {
  206.                 rs.close();
  207.             }catch (Exception e){
  208.                 e.printStackTrace();
  209.             }
  210.         }
  211.     }
  212. }
  213.  
  214.  
  215. ====================================================
  216. package com.ruqi.prepareStatment;
  217. import java.math.BigDecimal;
  218. import java.sql.Date;
  219. public class ScoresObject {
  220.     private int id;
  221.     private BigDecimal score;
  222.     private Date date;
  223.  
  224.     public ScoresObject() {
  225.     }
  226.  
  227.     public ScoresObject(int id, double score, Date date) {
  228.     }
  229.  
  230.     public int getId() {
  231.         return id;
  232.     }
  233.  
  234.     public void setId(int id) {
  235.         this.id = id;
  236.     }
  237.  
  238.     public BigDecimal getScore() {
  239.         return score;
  240.     }
  241.  
  242.     public void setScore(BigDecimal score) {
  243.         this.score = score;
  244.     }
  245.  
  246.     public Date getDate() {
  247.         return date;
  248.     }
  249.  
  250.     public void setDate(Date date) {
  251.         this.date = date;
  252.     }
  253.  
  254.     @Override
  255.     public String toString() {
  256.         return "ScoresObject{" +
  257.                 "id=" + id +
  258.                 ", score=" + score +
  259.                 ", date=" + date +
  260.                 '}';
  261.     }
  262. }
  263.

操作blob大数据类型

  1. package com.ruqi.prepareStatment;
  2.  
  3. import org.junit.Test;
  4.  
  5. import java.io.File;
  6. import java.io.FileInputStream;
  7. import java.io.FileOutputStream;
  8. import java.io.InputStream;
  9. import java.sql.Blob;
  10. import java.sql.Connection;
  11. import java.sql.PreparedStatement;
  12. import java.sql.ResultSet;
  13.  
  14. public class InsertBlob {
  15.  
  16.  
  17.     // 数据库的blob类型分别对应存储的最大值：tinyblob=255b; blob=65K; mediumblob=16M; longblob=4G
  18.     @Test
  19.     public void insertBlob() throws Exception {
  20.         Connection conn = ConnectUtil.getConnection();
  21.         String sql = "insert into Scores(photo) values (?);";
  22.         PreparedStatement ps = conn.prepareStatement(sql);
  23.         FileInputStream inputstream = new FileInputStream(new File("img.png"));
  24.         ps.setBlob(1,inputstream);
  25.         ps.executeUpdate();
  26.         ConnectUtil.closeConnection(conn,ps,null);
  27.  
  28.     }
  29.     @Test
  30.     public void selectBlob() throws Exception {
  31.         Connection conn = ConnectUtil.getConnection();
  32.         String sql = "select photo from Scores where id = ?;";
  33.         PreparedStatement ps = conn.prepareStatement(sql);
  34.         ps.setObject(1,7);
  35.         ResultSet rs = ps.executeQuery();
  36.         if(rs.next()){
  37.             Blob photo = rs.getBlob("photo");
  38.             InputStream is = photo.getBinaryStream();
  39.             FileOutputStream fos = new FileOutputStream("test.png");
  40.             byte[] buffer = new byte[1024];
  41.             int len;
  42.             while ((len = is.read(buffer)) != -1){
  43.                 fos.write(buffer,0 ,len);
  44.             }
  45.             fos.close();
  46.             is.close();
  47.         }
  48.         ConnectUtil.closeConnection(conn,ps,rs);
  49.  
  50.  
  51.     }
  52. }

批量插入数据

  1. public void insertManyData()  {
  2.         //由于mysql默认不支持批处理，因此需要在配置文件加以下配置url=jdbc:mysql://10.0.2.4:63306/test1111111?rewriteBatchedstatements=true
  3.         Connection conn = null;
  4.         PreparedStatement ps = null;
  5.         try{
  6.             conn = ConnectUtil.getConnection();
  7.             String sql = "insert into Scores (id) values(?);";
  8.             ps = conn.prepareStatement(sql);
  9.             conn.setAutoCommit(false);//设置为不自动提前
  10.             for (int i = 0; i <= 20000; i++) {
  11.                 ps.setObject(1,i);
  12.                 ps.addBatch();//攒sql
  13.                 if(i % 500 == 0 ){
  14.                     System.out.println(1);
  15.                     ps.executeBatch(); // 每攒500条执行一次
  16.                     ps.clearBatch();// 清除sql
  17.                 }
  18.             }
  19.             conn.commit();
  20.         }catch (Exception e){
  21.             e.printStackTrace();
  22.         }finally {
  23.             ConnectUtil.closeConnection(conn,ps,null);
  24.         }
  25.  
  26.  
  27.  
  28.     }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/575444
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号