电商网站建设咨询自媒体怎么入门
目 录
一、实例
二、获取结果集元数据
三、 获取新增数据行的主键值
四、封装工具类
一、实例
# jdbc.cjjdbc.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=rootpublic class JDBCSelectTest {public static void main(String[] args) {ResourceBundle bundle = ResourceBundle.getBundle("jdbc.cjjdbc");String driver = bundle.getString("driver");String url = bundle.getString("url");String user = bundle.getString("user");String password = bundle.getString("password");Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {// 1.注册驱动Class.forName(driver);// 2.获取连接connection = DriverManager.getConnection(url, user, password);// 3.获取数据库操作对象statement = connection.createStatement();// 4.执行SQL语句String selectSQL = "select id, name, realname, gender, phone from t_user";resultSet = statement.executeQuery(selectSQL);// 5.处理结果while (resultSet.next()) {// 根据查询结果集的列名获取当前行数据int id = resultSet.getInt("id");/*也可以全部通过 String 获取String id1 = resultSet.getString("id");*/String name = resultSet.getString("name");String realName = resultSet.getString("realname");String gender = resultSet.getString("gender");String phone = resultSet.getString("phone");System.out.println(id + "\t" + name + "\t" + realName + "\t" + gender + "\t" + phone);}/*while (resultSet.next()) {// 根据查询结果集的下标获取当前行数据int id = resultSet.getInt(1);String name = resultSet.getString(2);String realName = resultSet.getString(3);String gender = resultSet.getString(4);String phone = resultSet.getString(5);System.out.println(id + "\t" + name + "\t" + realName + "\t" + gender + "\t" + phone);}*/} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {// 6.释放资源if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}
}在处理查询结果集的时候：
1.可以通过 String 类型获取，也可以根据字段指定类型获取。
2.可以通过结果集中的列名获取，也可以通过结果集中的列索引下标获取，下标从 1 开始。
二、获取结果集元数据
设想一下，如果通过【select * 】来查询数据，但是想要知道结果集中每一列的列名，该如何操作呢？
public class ResultSetMetadata {public static void main(String[] args) {ResourceBundle bundle = ResourceBundle.getBundle("jdbc.cjjdbc");String driver = bundle.getString("driver");String url = bundle.getString("url");String user = bundle.getString("user");String password = bundle.getString("password");Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {Class.forName(driver);connection = DriverManager.getConnection(url, user, password);statement = connection.createStatement();String selectSQL = "select * from t_user";resultSet = statement.executeQuery(selectSQL);/* 通过结果集元数据获取列信息 */ResultSetMetaData metaData = resultSet.getMetaData();// 获取列数int columnCount = metaData.getColumnCount();System.out.println("列数：" + columnCount);for (int i = 1; i <= columnCount; i++) {// 获取列名String columnName = metaData.getColumnName(i);System.out.print(columnName + " ");// 获取列类型String columnTypeName = metaData.getColumnTypeName(i);System.out.print(columnTypeName + " ");// 获取列长度int columnLength = metaData.getColumnDisplaySize(i);System.out.print(columnLength + " ");}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}
}
三、 获取新增数据行的主键值
很多表的主键值都是自增非空的，在某些特殊业务环境下，插入新数据后希望获取这条新数据的主键值，应该如何获取呢？
可以使用 Statement 接口的 executeUpdate() 方法的重载版本，接收一个额外的参数，用于指定是否需要获取自动生成的主键值。然后通过下述步骤获取新插入的主键值：
（1）执行 executeUpdate() 方法时指定一个标志位，表示需要返回插入的主键值；
（2）调用 Statement 对象的 getGeneratedKeys() 方法，返回一个包含插入的主键值的 ResultSet 对象。
public class GetGeneratedKeysTest {public static void main(String[] args) {ResourceBundle bundle = ResourceBundle.getBundle("jdbc.cjjdbc");String driver = bundle.getString("driver");String url = bundle.getString("url");String user = bundle.getString("user");String password = bundle.getString("password");Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {Class.forName(driver);connection = DriverManager.getConnection(url, user, password);statement = connection.createStatement();String insertSQL = "insert into t_user values(null, '1329008366', 'lsf1793', '张佳', '女', '13503209981'),(null, '7382908677', 'zyn3306', '陈烨', '男', '13623845507')";int count = statement.executeUpdate(insertSQL, Statement.RETURN_GENERATED_KEYS);System.out.println("新插入数据条数：" + count);resultSet = statement.getGeneratedKeys();while (resultSet.next()) {long id = resultSet.getLong(1);System.out.println("新插入数据的id：" + id);}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}
}
四、封装工具类
在使用 JDBC 进行增、删、改、查操作时，绑定资源、加载驱动、获取连接、释放资源等代码是类似的，可以将其封装为一个工具类，降低代码冗余。
public class DbUtils {// 工具类的构造方法都是私有的，不能被实例化。private DbUtils() {}// 静态变量private static String driver;private static String url;private static String user;private static String password;static {// 读取属性配置文件ResourceBundle bundle = ResourceBundle.getBundle("jdbc.cjjdbc");driver = bundle.getString("driver");url = bundle.getString("url");user = bundle.getString("user");password = bundle.getString("password");// 注册驱动只需要执行一次，所以放在静态代码块中，在类加载时执行try {Class.forName(driver);} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {Connection connection = DriverManager.getConnection(url, user, password);return connection;}public static void close(Connection connection, Statement statement, ResultSet resultSet) {if (resultSet != null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}
}public class GetGeneratedKeysTest {public static void main(String[] args) {Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {connection = DbUtils.getConnection();statement = connection.createStatement();String insertSQL = "insert into t_user values(null, '1329008366', 'lsf1793', '张佳', '女', '13503209981'),(null, '7382908677', 'zyn3306', '陈烨', '男', '13623845507')";int count = statement.executeUpdate(insertSQL, Statement.RETURN_GENERATED_KEYS);System.out.println("新插入数据条数：" + count);resultSet = statement.getGeneratedKeys();while (resultSet.next()) {long id = resultSet.getLong(1);System.out.println("新插入数据的id：" + id);}} catch (SQLException e) {e.printStackTrace();} finally {DbUtils.close(connection, statement, resultSet);}}
}