Java基础：数据库连接 操作 增删改查

Java基础：数据库连接 操作 增删改查

创建表

在数据库中创建表

idea导入jar包

官网下载 mysql-connector-java.jar

在idea中新建lib包，将mysql-connector-java.jar复制到lib包下

代码

import java.sql.*;

public class test {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //加载驱动...mysql6.0之后需要加 cj
        Class.forName("com.mysql.cj.jdbc.Driver");

        //用户信息和url
        String url = "jdbc:mysql://localhost:3306/表名?serveTimezone=GMT%2B8";
        String username = "root";
        String password = "mysql密码";
				
      	//连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //创建对象，用来执行SQL
        Statement statement = connection.createStatement();
				//SQL语句
        String sql = "SELECT * FROM user";  //查询user表下的内容
				//执行SQL语句
        ResultSet resultSet = statement.executeQuery(sql);
				//将所有查询到的内容遍历输出
        while (resultSet.next()){
            System.out.println("id= "+ resultSet.getObject("ID"));
            System.out.println("name= "+ resultSet.getObject("name"));
            System.out.println("age= "+ resultSet.getObject("age"));
            System.out.println("==================");
        }
				//释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

​ 代码的加载驱动、用户信息及连接数据库等内容在后续的程序中都是重复的，所以可以创建工具类用来存放这些内容

工具类

在工程中新建一个util包，包中新建一个类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DbUtil {
        private static String jdbcName = "com.mysql.cj.jdbc.Driver";
        private static String url = "jdbc:mysql://localhost:3306/表名?serveTimezone=GMT%2B8";
        private static String userName = "root";
        private static String userPwd = "mysql密码";
        private static Connection con;
        public static Connection getCon() throws Exception {
            Class.forName(jdbcName);
            con = DriverManager.getConnection(url, userName, userPwd);
            return con;
        }
        public static void close(ResultSet res, Statement sta,Connection con) throws Exception {
            if (res != null){
                res.close();
            }
            if (sta != null){
                sta.close();
            }
            if (con != null) {
                con.close();
            }
        }
    }

增

创建工具类后，可减少重复部分

import util.DbUtil;
import java.sql.Connection;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection connection = DbUtil.getCon();

        Statement statement = connection.createStatement();

          String sql = " INSERT INTO `user`(`name`,`age`) VALUES ('张三','13')";
          int i = statement.executeUpdate(sql);
          if (i>0) {
              System.out.println("添加成功");
          }

        DbUtil.close(null,statement,connection);

    }
}

删

import com.hebeu.util.DbUtil;

import java.sql.Connection;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection connection = DbUtil.getCon();

        Statement statement = connection.createStatement();

          String sql = " DELETE FROM `user` WHERE id = 1";
          int i = statement.executeUpdate(sql);
          if (i>0) {
              System.out.println("删除成功");
          }

        DbUtil.close(null,statement,connection);


    }
}

改

import com.hebeu.util.DbUtil;

import java.sql.Connection;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection connection = DbUtil.getCon();

        Statement statement = connection.createStatement();

          String sql = "UPDATE `user` SET `name` = '张三' WHERE id = 1";
          int i = statement.executeUpdate(sql);
          if (i>0) {
              System.out.println("更新成功");
          }


        DbUtil.close(null,statement,connection);


    }
}

查

import com.hebeu.util.DbUtil;

import java.sql.Connection;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection connection = DbUtil.getCon();

        Statement statement = connection.createStatement();
      
     	  String sql = "SELECT * FROM user"; 
        ResultSet resultSet = statement.executeQuery(sql);		
        while (resultSet.next()){
            System.out.println("id= "+ resultSet.getObject("ID"));
            System.out.println("name= "+ resultSet.getObject("name"));
            System.out.println("age= "+ resultSet.getObject("age"));
            System.out.println("==================");
        }

        DbUtil.close(null,statement,connection);


    }
}

以上为本人学习Java时的一些笔记以及自己的理解

如有不足之处请大家指正