JDBC(MySQL)

​ 使用JDBCUtils的优点，在我们有大量使用mysql的数据库的情况下，我们可以通过更改jdbc.properties配置文件就可以修改数据库的配置，而不是寻找代码然后在一次次更改代码中的数据

properties文件，文件名user.properties

user=pxx
password=123
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/pxx

//通过propertices获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JavaTest/day2/user.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //注册驱动，建议写上兼容之前的版本
        Class.forName(driver);
        //得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //Mysql语句
        String sql ="insert into students values(?,?,?)";
        //preparedStatement对象实现PreparedStatement接口的实现类的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给？赋值
        preparedStatement.setInt(1,102192121);
        preparedStatement.setString(2, "彭大旧");
        preparedStatement.setString(3,"男");
        //执行sql语句executeUpdate(),返回影响行数
        // 执行查询语句executeQuery(),返回ResultSet对象，要用while循环，next是指向结果下一行
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt(1);  //第一列
            String name = resultSet.getString(2);
            String gender = resultSet.getString(3);
            System.out.println(id + "\t" + name + "\t" +gender);
        }
        // execute()执行任意sql语句返回布尔值

        int row = preparedStatement.executeUpdate();
        System.out.println(row>0?"successful":"fault");
        //关连接
        preparedStatement.close();
        connection.close();

JDBCUtils

public class JDBCUtils {


   private static String url;
   private static String user;
   private static String password;
   private static String driver;

   static {

       try {
           //1. 创建Properties集合类。
           Properties properties = new Properties();

           //获取src路径下的文件的方式--->ClassLoader 类加载器
           ClassLoader classLoader = JDBCUtils.class.getClassLoader();
           URL res  = classLoader.getResource("jdbc.properties");
           String path = res.getPath();
           //2. 加载文件
           properties.load(new FileReader(path));

           url = properties.getProperty("url");
           user = properties.getProperty("user");
           password = properties.getProperty("password");
           driver = properties.getProperty("driver");

           //注册驱动
           Class.forName(driver);


       } catch (IOException e) {
           e.printStackTrace();
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }


   }


   /**
    * 获取连接
    * @return 连接对象
    */
   public static Connection getConnection() throws SQLException {

       return DriverManager.getConnection(url, user, password);
   }


   /**
    * 释放资源
    * @param statement
    * @param connection
    */

   public static void close(Statement statement, Connection connection) {
       if (statement != null) {
           try {
               statement.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }
       if (connection != null) {
           try {
               connection.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }

   }

   /**
    * 释放资源
    * @param resultSet
    * @param statement
    * @param connection
    */
   public static void close(Statement statement, Connection connection,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();
           }
       }

   }

}



/**
* JDBCUtils的测试类
* 
*/
public class JDBCUtilsDemo {

   public static void main(String[] args) {

       List<student> students = new JDBCUtilsDemo().finAll();
       System.out.println(students);
       System.out.println(students.size());

   }

   public List<student> finAll() {

       Connection connection = null;
       PrepareStatement prepareStatement = null;
       ResultSet resultSet = null;
       String sql = "SELECT * from student where id = ?";

       try {

           //1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
           //2. 注册驱动
           //以上2步骤都通过JDBCUtils来简化了
           connection=JDBCUtils.getConnection();
           //3.获取执行sql的对象
           prepareStatement = connection.prepareStatement(sql);
           prepareStatement.setInt(1,102)
           //4.执行sql
           resultSet = prepareStatement.executeQuery();
           //5.遍历结果集，封装对象，装载集合
           student stu = null;
           list = new ArrayList<student>();
           while (resultSet.next()) {
               int id = resultSet.getInt("id");
               String ename = resultSet.getString("name");
               System.out.println(id + "\t" + name + "\t");
           }

       } catch (SQLException throwables) {
           throwables.printStackTrace();
       } finally {
           JDBCUtils.close(statement,connection,resultSet);
       }
       
   }
}