15. 基于自动化–JAVA之JDBC
一、JDBC操作数据库
JDBC:Java database connection,Java数据库连接技术,通过代码来连接数据库,操作数据库的一种技术
环境搭建:导入jar包依赖
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency>
数据查询案例
public class JDBCDemo1 { public static void main(String[] args) { JDBCDemo1 demo1 = new JDBCDemo1(); demo1.query(); } //数据库查询 public void query() { //?代表占位符 String sql = "SELECT id,name FROM falcon_c4_mobile_city WHERE code = ?"; try { Properties properties = new Properties(); InputStream is = new FileInputStream(new File("src/main/resources/Projects/Falcon/Env/jdbc.properties")); properties.load(is); String url = properties.getProperty("jdbc.url"); String username = properties.getProperty("jdbc.username"); String password = properties.getProperty("jdbc.password"); //1.根据连接信息,获得数据库连接(连接数据库) Connection connection = DriverManager.getConnection(url,username,password); //2.获取PreparedStatement对象(此类型的对象有提供数据库操作方法) PreparedStatement preparedStatement = connection.prepareStatement(sql); //3.设置条件字段的值 preparedStatement.setObject(1,"110100"); //4.调用查询方法,执行查询,返回ResultSet结果集 ResultSet resultSet = preparedStatement.executeQuery(); //5.从结果集取出查询数据 while (resultSet.next()){ String id = resultSet.getObject("id").toString(); String name = resultSet.getObject("name").toString(); System.out.println("id:"+id+",name:"+name); } } catch (Exception e) { e.printStackTrace(); } } } jdbc.properties文件 jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://1.1.1.1:3306/falcon?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 jdbc.username=test jdbc.password=test
二、数据工具类封装
public class JDBCUtils { public static Properties properties = new Properties(); static { System.out.println("静态代码块解析properties数据"); InputStream is; try { is = new FileInputStream(new File("src/main/resources/db.properties")); properties.load(is); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public static Map<String,Object> query(String sql, Object ... values){ Map<String,Object> columnLabelAndValues = null; try { Connection connection = getConnection(); //2.获取PreparedStatement对象(此类型的对象有提供数据库操作方法) PreparedStatement preparedStatement = connection.prepareStatement(sql); //3.设置条件字段的值 for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); } //4.调用查询方法,执行查询,返回ResultSet结果集 ResultSet resultSet = preparedStatement.executeQuery(); //获取查询相关信息 ResultSetMetaData metaData = resultSet.getMetaData(); //得到查询字段的数目 int columnCount = metaData.getColumnCount(); //5.从结果集取出查询数据 columnLabelAndValues = new HashMap<>(); while (resultSet.next()){ //循环取出每个查询字段的数据 for (int i = 1; i <= columnCount; i++) { String columnLable = metaData.getColumnLabel(i); String columnValue = resultSet.getObject(columnLable).toString(); columnLabelAndValues.put(columnLable,columnValue); } } } catch (Exception e) { e.printStackTrace(); } return columnLabelAndValues; } public static Connection getConnection() throws SQLException { String url = properties.getProperty("jdbc.url"); String username = properties.getProperty("jdbc.username"); String password = properties.getProperty("jdbc.password"); //1.根据连接信息,获得数据库连接(连接数据库) return DriverManager.getConnection(url, username, password); } }