黑马程序员技术交流社区

标题: 查询数据库中USER表的数据 [打印本页]

作者: 刘邦m 时间: 2019-4-17 19:09
标题: 查询数据库中USER表的数据
SelectUser

package Java_EE.MySQL.day04.JDBC_Demo2_DQL;
import Java_EE.MySQL.util.JDBCUtils01;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DemoSelectUser {
public static void main(String[] args) {
      List<User> users = JdbcSelect();  //调用查询数据表
      for (User user : users) {
         System.out.println(user);
      }
}
//查询数据库db1中的数据表user的数据
public static List<User> JdbcSelect() {
      ArrayList<User> users = new ArrayList<>();
      Connection conn = null;
      PreparedStatement pstat = null;
      ResultSet rs = null;
      try {
         //第一步：注册驱动
//          Class.forName("com.mysql.jdbc.Driver");
         //第二步：获取数据库连接对象
//          conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root");
         conn= JDBCUtils01.getConnection();
         conn.setAutoCommit(false);//开启事务
         //第三，四步：定义SQL语句，获取执行SQL的对象
         pstat = conn.prepareStatement("select * from USER");
         //第五步：执行SQL
         rs = pstat.executeQuery();
         //第六步：处理结果
         User user = null;//定义User对象，准备接收查询出来的数据
         while(rs.next()){
            //接收数据
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String password = rs.getString("password");
            //添加到集合
            user = new User(id,name,password);
            users.add(user);
         }
         conn.commit();//提交事务
      } catch (Exception e) {
         if(conn != null){
            try {
                  conn.rollback();//只要有任何的异常发生，事务都会“回滚”
            } catch (SQLException e1) {
                  e1.printStackTrace();
            }
         }
         e.printStackTrace();
         //第七步：关闭资源
      }finally {
         JDBCUtils01.close(rs,pstat,conn);
/*
         if(rs != null){
            try {
                  rs.close();
            } catch (SQLException e) {
                  e.printStackTrace();
            }
         }
         if(pstat != null){
            try {
                  pstat.close();
            } catch (SQLException e) {
                  e.printStackTrace();
            }
         }
         if(conn != null){
            try {
                  conn.close();
            } catch (SQLException e) {
                  e.printStackTrace();
            }
         }
*/
      }
      return users;
}
}

User

package Java_EE.MySQL.day04.JDBC_Demo2_DQL;
public class User {
private int id;
private String username;
private String password;
public User(){}
public User(int id, String username, String password) {
      this.id = id;
      this.username = username;
      this.password = password;
}
public int getId() {
      return id;
}
public void setId(int id) {
      this.id = id;
}
public String getUsername() {
      return username;
}
public void setUsername(String username) {
      this.username = username;
}
public String getPassword() {
      return password;
}
public void setPassword(String password) {
      this.password = password;
}
@Override
public String toString() {
      return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            '}';
}
}

JDBCUtils01

package Java_EE.MySQL.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类
*/
public class JDBCUtils01 {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
   * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
   */
static{
      //读取资源文件，获取值。
      try {
         //1. 创建Properties集合类。
         Properties pro = new Properties();
         //获取src路径下的文件的方式--->ClassLoader 类加载器
         ClassLoader classLoader = JDBCUtils01.class.getClassLoader();
         URL res  = classLoader.getResource("Java_EE/MySQL/jdbc.properties");
         String path = res.getPath();
         System.out.println(path);
         //2. 加载文件
         // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\src\\jdbc.properties"));
         pro.load(new FileReader(path));
         //3. 获取数据，赋值
         url = pro.getProperty("url");
         user = pro.getProperty("user");
         password = pro.getProperty("password");
         driver = pro.getProperty("driver");
         //4. 注册驱动
         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 stmt
   * @param conn
   */
public static void close(Statement stmt,Connection conn){
      close(null, stmt, conn);
      /*if( stmt != null){
         try {
            stmt.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if( conn != null){
         try {
            conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }*/
}
/**
   * 释放资源
   * @param rs
   * @param stmt
   * @param conn
   */
public static void close(ResultSet rs,Statement stmt, Connection conn){
      if( rs != null){
         try {
            rs.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if( stmt != null){
         try {
            stmt.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if( conn != null){
         try {
            conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
}
}

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2