Java之JDBC

JDBC是什么

jdbc是一套API，是一套操作数据库的标准类库为了统一操作各个厂商的数据库，而制定的一套标准有了这样一套标准之后，数据库厂商提供实现，即可完成对应的操作

JDBC示例（以MySQL为例） 下面是步骤

导入gradle驱动 bulid.gradle下 compile('mysql:mysql-connector-java:8.0.20') 新建测试表 CREATE DATABASE IF NOT EXISTS course DEFAULT CHARSET utf8mb4; USE course; SET NAMES utf8mb4;

建用户表 CREATE TABLE IF NOT EXISTS `tb_user` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', `name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '用户名字', `age` INT NOT NULL DEFAULT 0 COMMENT '年纪', PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='用户表'; 测试数据 -- 测试数据 insert into `tb_user`(`name`,`age`) values('张三',23);

第一个示例：读取数据

public class Demo2 { public static void main(String[] args) throws SQLException { //http:// //https:// //tcp:// //dubbo:// String url = "jdbc:mysql://127.0.0.1:3306/course"; String username = "root"; String password = "089757abc"; String sql = "select * from tb_user"; //1.建立连接 Connection connection = DriverManager.getConnection(url, username, password); //2.具体操作 //2.1 预编译SQL PreparedStatement statement = connection.prepareStatement(sql); //2.2执行查询 ResultSet resultSet = statement.executeQuery(); //2.3 处理结果集 while (resultSet.next()) { //根据表设计的字段 int id = resultSet.getInt("id"); String name = resultSet.getString("name"); int age = resultSet.getInt("age"); System.out.println("id = " + id+",name = " + name+",age = " + age); } // 3.资源关闭 resultSet.close(); statement.close(); connection.close(); } }

ps:如果库里字段起别名了，需要写别名的字段

CRUD 查询见上面

插入 executeUpdate() 写死型 public class Demo4 { static String URL = "jdbc:mysql://127.0.0.1:3306/course"; static String USER_NAME = "root"; static String PASSWORD = "089757abc"; public static void main(String[] args) { testInsert(); } public static void testInsert() { String sql = "insert into `tb_user`(`name`,`age`) values('蔡徐坤','88')"; Connection connection = null; PreparedStatement statement = null; try { //建立连接 connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD); //创建tatementS并执行update statement = connection.prepareStatement(sql); int effectRows = statement.executeUpdate(); System.out.println("insert effectRows = " + effectRows); } catch (SQLException e) { e.printStackTrace(); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }

不写死

String sql = "INSERT INTO `tb_user`(`name`,`age`) VALUES(?,?)"; Connection connection = null; PreparedStatement statement = null; try { //建立连接 connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD); //创建tatementS并执行update statement = connection.prepareStatement(sql); statement.setString(1,"花泽香菜"); statement.setInt(2,18); 修改 和插入类似，修改sql语句即可 String sql = "update tb_user set name='徐良' where id=4"; System.out.println("update effectRows = " + effectRows);

封装方法，然后CRUD

public class DemoForWrite { static String URL = "jdbc:mysql://127.0.0.1:3306/course"; static String USER_NAME = "root"; static String PASSWORD = "089757abc"; public static int modify(String sql, List<Object> params) { try (Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD); PreparedStatement statement = connection.prepareStatement(sql);) { for (int i = 0; i < params.size(); i++) { statement.setObject(i + 1, params.get(i)); } return statement.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } } } 调用此方法 private static void testDelete() { String sql = "delete from tb_user where id=? "; ArrayList<Object> params = Lists.newArrayList(); params.add(1); int rows = DemoForWrite.modify(sql, params); System.out.println("rows = " + rows); } private static void testUpdate() { String sql = "update tb_user set name=?,age=? where id=?"; ArrayList<Object> params = Lists.newArrayList(); params.add("林俊杰"); params.add(3); params.add(2); int rows = DemoForWrite.modify(sql, params); System.out.println("rows = " + rows); } private static void testInsert() { String sql = "insert into tb_user(name,age) values(?,?)"; ArrayList<Object> params = Lists.newArrayList(); params.add("金沙"); params.add(19); int rows = DemoForWrite.modify(sql, params); System.out.println("rows = " + rows); } }

对查询做一个小小的设计 将user 抽象出 对象实体 引用的是模板设计模式 难点

tudo

JDBC还是有用处