JDBC进行增删查改

JDBC进行增删改查

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

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

工具集：IDEA 2018.2.25，MySQL5.5

下载JDBC的jar包

• 在mysql官网上进行下载对应的压缩包

导入JDBC的jar包

• 解压下载好的压缩包

• 导入到IDEA中

  点击project structure->Libraries->+号->java，找到解压的路径，选择添加

• 封装数据库连接和关闭

	 	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());