JDBC中的statement对象用于向数据库发送SQL语句,想完成数据的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法用于向数据库发送增删改查的SQL语句,executeUpdate执行完成后,将会返回一个整数,即增删改语句导致了数据库几行数据发生了变化。
Statement对象的executeQuery方法用于向数据库发送查询语句,executeUpdate方法放回代表代表查询结果的ResultSet对象。
driver = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true username = root password = 123456
注意:每条语句结束后不能有“;”
package utils; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * 用于加载db.properties文件资源、加载驱动、获取连接、释放连接资源等 */ public class JdbcUtils { //属性信息 private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ //加载资源 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); //获取资源 Properties properties = new Properties(); properties.load(in); //获取详细信息 driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只用加载一次 Class.forName(driver); } catch(IOException | ClassNotFoundException e){ e.printStackTrace(); } catch(Exception e){ e.printStackTrace(); } } /** * 获取连接 * @return * @throws SQLException */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } /** * 释放连接资源 * @param connection * @param statement * @param resultSet */ public static void release(Connection connection, Statement statement, ResultSet resultSet){ //关系的顺序依次为resultSet、statement、connection if(resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } catch(Exception e){ e.printStackTrace(); } } if(statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } catch(Exception e){ e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } catch(Exception e){ e.printStackTrace(); } } } }
文件层级如下:
(1)插入操作:使用executeUpdate(String sql)方法完成数据添加操作
package com.study.lesson; import utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 测试insert */ public class JdbcDemo02 { public static void main(String[] args) { //属性信息 Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { //连接驱动,获得数据库 connection = JdbcUtils.getConnection(); //创建执行SQL语句的对象 statement = connection.createStatement(); //SQL插入语句 String sql ="INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)" + "VALUES(4,'zhaoliu','123456','123456789@qq.com','2019-02-03')"; //执行SQL语句,返回代码执行后受影响的行数 int i = statement.executeUpdate(sql); if(i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } finally { //释放连接资源 JdbcUtils.release(connection,statement,resultSet); } } }
输出结果:在SQLyog中可查看对应变化
插入成功!
(2)删除操作:使用executeUpdate(String sql)方法完成数据删除操作
package com.study.lesson; import utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 测试delete */ public class JdbcDemo03 { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql ="DELETE FROM `users` WHERE `id` = 4;"; int i = statement.executeUpdate(sql); if(i>0){ System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } finally { JdbcUtils.release(connection,statement,resultSet); } } }
输出结果:在SQLyog中可查看对应变化
删除成功!
(3)更新操作:使用executeUpdate(String sql)方法完成数据更新操作
package com.study.lesson; import utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 测试update */ public class JdbcDemo04 { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql ="UPDATE `users` SET `password`='111111' WHERE `id`=1;"; int i = statement.executeUpdate(sql); if(i>0){ System.out.println("更新成功!"); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } finally { JdbcUtils.release(connection,statement,resultSet); } } }
输出结果:在SQLyog中可查看对应变化
更新成功!
(4)查询操作:使用executeUpdate(String sql)方法完成数据查询操作
package com.study.lesson; import utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 测试select */ public class JdbcDemo05 { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql ="SELECT * FROM `users` WHERE `id`=1;"; resultSet = statement.executeQuery(sql); while (resultSet.next()){ System.out.println(resultSet.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } finally { JdbcUtils.release(connection,statement,resultSet); } } }
输出结果:
zhangsan