JDBC工具类:
抽取JDBC工具类:JDBCUtils
目的:简化书写
分析∶
1.注册驱动也抽取
2.抽取一个方法获取连接对象
3.抽取一个方法释放资源
代码实现:
/*** JDBC工具类*/ public class JDBCUtils {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 = JDBCUtils.class.getClassLoader();URL res = classLoader.getResource("jdbc.properties");String path = res.getPath();System.out.println(path);// 2.加载文件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 stem* @param conn*/public static void close(Statement stem, Connection conn, ResultSet rs) {if (stem !=null ) {try {stem.close();} catch (SQLException es) {es.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}} }
public static void main(String[] args) {List<Emp> list = new JDBCDemo8().findAll1();System.out.println(list);}public List<Emp> findAll1() {List<Emp> list = null;ResultSet res = null;Statement sta = null;Connection conn = null;try { // // 1.注册驱动成功 // Class.forName("com.mysql.jdbc.Driver"); // // 2.数据库连接 // conn = DriverManager.getConnection("jdbc:mysql:///mybatis", "root", "root");conn = JDBCUtils.getConnection();// 3.指定sql语句String sql = "select * from emp";// 4.获取sql对象语句sta = conn.createStatement();// 5.执行sqlres = sta.executeQuery(sql);// 6.遍历结果集,封装对象,装载集合Emp emp = null;list = new ArrayList<>();while (res.next()) {// 获取数据int id = res.getInt("id");String name = res.getString("name");String gender = res.getString("gender");double salary = res.getDouble("salary");long join_date = res.getLong("join_date");int dept_id = res.getInt("dept_id");// 创建emp对象emp = new Emp();emp.setId(id);emp.setName(name);emp.setGender(gender);emp.setSalary(salary);emp.setJoin_date(join_date);emp.setDept_id(dept_id);//装载集合 list.add(emp);}} catch (SQLException es) {es.printStackTrace();} finally { // // 7.释放资源 // // 避免空指针异常 // if (conn != null) { // try { // conn.close(); // } catch (SQLException e) { // e.printStackTrace(); // } // } // } // // 7.释放资源 // // 避免空指针异常 // if (sta != null) { // try { // sta.close(); // } catch (SQLException e) { // e.printStackTrace(); // } // } // // 7.释放资源 // // 避免空指针异常 // if (res != null) { // try { // res.close(); // } catch (SQLException e) { // e.printStackTrace(); // } JDBCUtils.close(sta,conn,res);}return list;}
输出结果:
JDBC登录案例:
练习∶
需求:
1.通过键盘录入用户名和密码
2﹒判断用户是否登录成功
select* from user where username = "" and password = "";
如果这个sql有查询结果,则成功,反之,则失败
步骤:
1.创建数据库表user
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32),
PASSwORD VARCHAR( 32)
};
INSERT INTO USER VALUES(NULL, 'zhangsan' , '123");
INSERT INTO USER VALUES(NULL, 'lisi" ,'234' );
代码实现:
public static void main(String[] args) {// 1.键盘录入,接受用户名和密码Scanner sc = new Scanner(System.in);System.out.println("请输入用户名:");String username = sc.nextLine();System.out.println("请输入密码:");String password = sc.nextLine();// 2.调用方法boolean login = JDBCDemo9.login(username, password);// 3.判断结果,输出不同语句if (login) {// 登录成功System.out.println("登录成功");} else {System.out.println("用户名或密码错误!");}}/*** 登录方法* @param username* @param password* @return*/public static boolean login(String username, String password) {if (username == null || password == null) {return false;}//连接数据库判断是否登录成功Connection conn = null;Statement stm = null;ResultSet rs = null;// 1.获取连接try {conn = JDBCUtils.getConnection();// 2.定义sqlString sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";// 3.获取执行sql的对象stm = conn.createStatement();// 4.执行查询rs = stm.executeQuery(sql);// 5.判断return rs.next();//如果有下一行,则返回true} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtils.close(stm,conn,rs);}return false;}