JDBC进行增删查改

冷不防 2021-12-24 05:01 349阅读 0赞

# JDBC进行增删改查 #

本篇博客较为基础，只作学习记录之用。  
本篇分为：

*  下载JDBC的jar包
 *  导入JDBC的jar包
 *  封装数据库连接和关闭
 *  封装数据库操作
 *  测试

工具集：IDEA 2018.2.25，MySQL5.5

## 下载JDBC的jar包 ##

*  在[mysql官网][mysql]上进行下载对应的压缩包

## 导入JDBC的jar包 ##

*  解压下载好的压缩包![在这里插入图片描述][20190626191627187.png]
 *  导入到IDEA中
    
    点击project structure->Libraries->+号->java，找到解压的路径，选择添加![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvZGVyOTU3_size_16_color_FFFFFF_t_70]
 *  封装数据库连接和关闭

final String driver = "com.mysql.cj.jdbc.Driver";
            final String username = "*****";
            final String password = "*************";
            final String url = "jdbc:mysql://localhost:3306/jdbc_demo?serverTimezone=UTC";   //
    
    
            protected Connection DBconn(String driver,String username,String password) {
                try {
                    //加载驱动
                    Class.forName(driver);
                    //连接数据库
                    Connection connection = DriverManager.getConnection(url,username,password);
                    return connection;
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
    		
    		public void closeConn(ResultSet resultSet, Statement statement,Connection connection){
    	        try {
    	            if(resultSet!=null){
    	                resultSet.close();
    	            }
    	            if (statement!=null){
    	                statement.close();
    	            }
    	            if(connection!=null){
    	                connection.close();
    	            }
    	        } catch (SQLException e) {
    	            e.printStackTrace();
    	        }
        	}

*  封装数据库操作

//查函数
    	public List<Custom> querySQL(String sql){
            List<Custom> list = new ArrayList<>();
            Connection conn = DBconn(driver,username,password);
            try {
                PreparedStatement statement = conn.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    int ID = resultSet.getInt(1);
                    String name = resultSet.getString(2);
                    Custom custom = new Custom(ID,name);
                    list.add(custom);
                }
                closeConn(resultSet,statement,conn);
                return list;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    	
    	//增删改函数
        public boolean otherSQL(String sql){
            Connection conn = DBconn(driver,username,password);
            try {
                PreparedStatement statement = conn.prepareStatement(sql);
                statement.executeUpdate();
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }

*  测试数据库封装

DBconn dBconn = new DBconn();
            String sql = "select *from tb1";      //查操作
            List<Custom> temp = dBconn.querySQL(sql);
            System.out.println(temp.toString());
    
            String sql1 = "insert into tb1 values('3','王五')";       //增操作
            dBconn.otherSQL(sql1);
            temp = dBconn.querySQL(sql);
            System.out.println(temp.toString());
            
         	sql1 = "delete from tb1 where id = 3";   //删操作
            dBconn.otherSQL(sql1);
            temp = dBconn.querySQL(sql);
            System.out.println(temp.toString());
    		
    		sql1 = "update tb1 set name = '真帅' where id = 1";   //改操作
            dBconn.otherSQL(sql1);
            temp = dBconn.querySQL(sql);
            System.out.println(temp.toString());

[mysql]: https://dev.mysql.com/downloads/connector/j/
[20190626191627187.png]: /images/20211223/35dc65cfd35d4888bc32db7596344a0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvZGVyOTU3_size_16_color_FFFFFF_t_70]: /images/20211223/8f9c86ce08a741129cc348e6bc41d468.png