JDBC-Learn01
JDBC学习
- JDBC基本概念
- 快速入门
- 对JDBC中各个接口和类的详解
JDBC
1.概念
- 概念 : Java DataBase Connectivity Java数据库连接 Java语言操作数据库
- JDBC本质: 其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口, 各个数据库厂商去实现这套接口,提供数据库驱动jar包,我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类
图解实例
2.快速入门
步骤:
导入驱动jar包-mysql-connector-java-5.1.37-bin.jar
在项目目录下创建libs目录然后复制mysql-connector-java-5.1.37-bin.jar到项目的lib目录下.
选择导入的jar包-->Add As Library
注册驱动
获取数据库连接对象 Connection
定义sql
获取执行sql语句的对象 Statement
执行sql 接受返回结果
处理结果
释放资源
代码:
package com.levizhao.demo01.mysql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Demo01_JDBC { public static void main(String[] args) throws Exception { //1.导入驱动jar包 //2.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3.获取数据库连接对象 //Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "root"); Connection conn = DriverManager.getConnection("jdbc:mysql:///db4", "root", "root"); //4.定义sql语句 String sql = "update account set balance = 50000 where id"; //5.获取执行sql的对象Statement Statement stmt = conn.createStatement(); //6.执行sql int count = stmt.executeUpdate(sql); //7.处理结果 System.out.println(count); //8.释放资源 stmt.close(); conn.close(); } }
3.详解各个对象
DriverManager:驱动管理对象
功能:
驱动注册:告诉程序该使用哪一个数据库驱动jar
//static void registerDriver(Driver driver):注册与给定的驱动程序DriverManager //写代码使用:Class.forName("com.mysql.jdbc.Driver"); //通过查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块 static { try { DriverManager.registerDriver(new Driver()); } catch (SQLException var1) { throw new RuntimeException("Can't register driver!"); } } //注意:mysql5之后的确定jar包可以省略注册驱动的步骤.
获取数据库连接:
/* * 方法:static Connection getConnection(String url,String user, String password) * 参数: url:指定连接路径 *语法:jdbc:mysql://ip地址(域名):端口号/数据库名称 *例子:jdbc:mysql://localhost:3306/db3 *细节: 如果连接的是本地的mysql服务器,并且端口是3306,则url可以简写为:jdbc:mysql:///数据库名称 user:数据库用户名 password:数据库密码 */
Connection:数据库连接对象
1.功能:
获取执行sql的对象
- Statement createStatement()
- PreparedStatement prepareStatement(String sql)
. 管理事务:
- 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false, 即开启事务
- 提交事务:Commit()
- 回滚事务:rollback()
. Statement: 执行sql的对象
执行sql
boolean execute(String sql):可以执行任意的sql
int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句
// 返回值:影响的行数,可以通过这个影响的函数判断DML语句是否执行成功 返回值>=0的则执行成功,反之失败
代码1
package com.levizhao.demo01.mysql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * account 表 添加一条记录 insert语句 */ public class Demo02_JDBC { public static void main(String[] args) { Statement stmt = null; Connection conn = null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.定义sql String sql = "insert into account values(null,'赵增印',60000)"; //3.获取Connection对象 conn = DriverManager.getConnection("jdbc:mysql:///db4", "root", "root"); //4.获取执行sql的对象Statement stmt = conn.createStatement(); //5.执行sql int count = stmt.executeUpdate(sql); //影响的行数 //6.处理结果 System.out.println(count); if(count>0){ System.out.println("Add Sucessed"); }else{ System.out.println("Add Faild"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { //stmt.close(); //避免空指针异常 //7.释放资源 if (stmt!=null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } }
代码2
package com.levizhao.demo01.mysql; import java.sql.*; /** * account 修改表记录 */ public class Demo03_JDBC { public static void main(String[] args) { Connection conn = null; Statement stat = null; try { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 获取Connection对象 conn = DriverManager.getConnection("JDBC:mysql:///db4?useSSL=false","root","root"); //3. 获取执行sql对象Statement stat = conn.createStatement(); //4. 定义要执行的sql语句 String sql = "UPDATE account SET NAME=\"李四\",balance = 35000 WHERE id =2;"; //5. 获取执行sql语句返回的值 int count = stat.executeUpdate(sql); //6. 如果count大于等于0说明已经成功执行sql语句,如果小于0则说明sql语句没有执行 System.out.println(count); if (count>=0){ System.out.println("Data had modified"); }else{ System.out.println("Data no change"); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { //7. 关闭conn和stat,释放资源. if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } }
代码3
package com.levizhao.demo01.mysql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * account表 删除一条记录 */ public class Demo04_JDBC { public static void main(String[] args) { Connection conn = null; Statement stat = null; try { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 获取Connection对象 conn = DriverManager.getConnection("jdbc:mysql:///db4?useSSL=false","root","root"); //3. 获取执行sql对象Statement stat= conn.createStatement(); //4. 定义要执行的sql语句 String sql = "DELETE FROM account WHERE id =4"; //5. 获取执行sql语句返回的值 int i = stat.executeUpdate(sql); System.out.println(i); //6. 如果i大于等于0说明已经成功执行sql语句,如果小于0则说明sql语句没有执行 if (i>-0){ System.out.println("The record has been deleted"); }else{ System.out.println("The record no change"); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); }finally { //7. 关闭conn和stat,释放资源. if (conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } }
ResultSet executeQuery(String sql): 执行DQL(select)语句
. ResultSet:结果级对象,封装查询结果
- boolean next(): 游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
- getXXX(参数):获取数据
- xxx:代表数据类型 如: int getInt(), String getString()
- 参数:
- int :代表列的编号,从1开始 如:getInt(1)
- String: 代表列名称. 如:getDouble("balance")
- 注意:
- 使用步骤:
- 游标向下移动一行
- 判断是否有数据
- 获取数据
// 循环判断游标释放是最后一行末尾 while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name boolean gender = rs.getBoolean("gender"); Date birthday = rs.getDate("birthday"); System.out.println("编号: "+id+", 姓名: "+name+", 性别: "+gender+", 生日: "+birthday); }
代码
package com.levizhao.demo03.mysql; import java.sql.*; public class demo01_JDBC { public static void main(String[] args) { Connection conn = null; Statement stat = null; ResultSet resultSet =null; try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql:///db4?useSSL=false"; String username = "root"; String password = "root"; conn = DriverManager.getConnection(url, username, password); stat = conn.createStatement(); String sql = "select * from person"; resultSet = stat.executeQuery(sql); while(resultSet.next()){ int id = resultSet.getInt("id"); String name = resultSet.getString("name"); int age = resultSet.getInt("age"); double score = resultSet.getDouble("score"); String birthday = resultSet.getString("birthday"); String insert_time = resultSet.getString("insert_time"); System.out.println("id="+id+", "+"name="+name+", " +"age"+age+", "+"score="+score+", "+"birthday="+birthday+", "+ "insert_time="+insert_time ); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); }finally { if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
练习:
- 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。
1. 定义Emp类
2. 定义方法 public ListfindAll(){}
3. 实现方法 select * from emp;Emp.java类
// 查询emp表的数据将其封装为对象,然后打印 package com.levizhao.domain; public class Emp { private int id; private String name; private int age; private String sex; private String address; private int math; private int english; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public int getMath() { return math; } public void setMath(int math) { this.math = math; } public int getEnglish() { return english; } public void setEnglish(int english) { this.english = english; } @Override public String toString() { return "emp{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", sex='" + sex + '\'' + ", address='" + address + '\'' + ", math=" + math + ", english=" + english + '}'; } public void setEname(String ename) { } }
打印代码
package com.levizhao.demo03.mysql; import com.levizhao.domain.Emp; /** * * 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。 */ import java.sql.*; import java.util.ArrayList; import java.util.List; public class Test01_JDBC { public static void main(String[] args) { List all = new Test01_JDBC().findAll(); System.out.println(all); System.out.println(all.size()); } /** * 查询所有emp对象 * @return */ public List findAll(){ Connection conn = null; Statement stat = null; ResultSet resultSet =null; List<Emp> list = null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql:///db4?useSSL=false"; String username = "root"; String password = "root"; //2.获取连接 conn = DriverManager.getConnection(url, username, password); //4.获取执行sql的对象 stat = conn.createStatement(); //3.定义sql String sql = "select * from people"; //5.执行sql resultSet = stat.executeQuery(sql); //6.遍历结果集,封装对象,装载集合 Emp emp = null; list = new ArrayList<Emp>(); while(resultSet.next()){ //获取数据 int id = resultSet.getInt("id"); String name = resultSet.getString("name"); int age = resultSet.getInt("age"); String sex= resultSet.getString("sex"); int math = resultSet.getInt("math"); int english = resultSet.getInt("english"); // 创建emp对象,并赋值 emp = new Emp(); emp.setId(id); emp.setName(name); emp.setAge(age); emp.setSex(sex); emp.setMath(math); emp.setEnglish(english); //装载集合 list.add(emp); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); }finally { if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } return list; } }
抽取JDBC工具类:JDBCUtils
简化书写
分析:
- 注册驱动也抽取
- 抽取一个方法获取连接对象
- 抽取一个方法释放资源
驱动抽取
1.创建jdbc.properties文件
url=jdbc:mysql:///d3 user=root password=root driver=com.mysql.jdbc.Driver
2.创建JDBCUtil.java文件
package com.levizhao.util; /** * JDBC工具类 */ import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.net.URLDecoder; import java.sql.*; import java.util.Properties; public class JDBCUtils { private static String url; private static String username; private static String password; private static String driver; /** * 文件的读取,只需要读取一次即可拿到这些值。使用静态代码块 */ static { //读取资源文件,获取值。 try { //1. 创建Properties集合类。 Properties pro = new Properties(); //获取src路径下的文件的方式--->ClassLoader 类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL resource = classLoader.getResource("jdbc.properties"); String path = resource.getPath(); // 需要对路径进行转码,路径中有特殊字符或者空格如果不转码的话会获取失败 String ConfigPath= URLDecoder.decode(path, "utf-8"); pro.load(new FileReader(ConfigPath)); // 获取数据,赋值 url = pro.getProperty("url"); username = pro.getProperty("username"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); // 注册驱动 Class.forName(driver); } catch (IOException | ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } /** * * @param stmt * @param conn */ public static void close(Statement stmt, Connection conn){ if (stmt != null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.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 throwables) { throwables.printStackTrace(); } } if (conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stmt!= null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
代码
package cn.itcast.jdbc; import cn.itcast.domain.Emp; import cn.itcast.util.JDBCUtils; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * * 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。 */ public class JDBCDemo8 { public static void main(String[] args) { List<Emp> list = new JDBCDemo8().findAll2(); System.out.println(list); System.out.println(list.size()); } /** * 演示JDBC工具类 * @return */ public List<Emp> findAll2(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Emp> list = null; try { /* //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");*/ conn = JDBCUtils.getConnection(); //3.定义sql String sql = "select * from emp"; //4.获取执行sql的对象 stmt = conn.createStatement(); //5.执行sql rs = stmt.executeQuery(sql); //6.遍历结果集,封装对象,装载集合 Emp emp = null; list = new ArrayList<Emp>(); while(rs.next()){ //获取数据 int id = rs.getInt("id"); String ename = rs.getString("ename"); int job_id = rs.getInt("job_id"); int mgr = rs.getInt("mgr"); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); double bonus = rs.getDouble("bonus"); int dept_id = rs.getInt("dept_id"); // 创建emp对象,并赋值 emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); //装载集合 list.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally { /*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(); } }*/ JDBCUtils.close(rs,stmt,conn); } return list; } }
登录案例
package com.levizhao.demo03.jdbc; import com.levizhao.util.JDBClogin; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class Demo01_JDBC { public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("请输入用户名:"); String username = sc.nextLine(); System.out.println("请输入密码:"); String password = sc.nextLine(); boolean login = new Demo01_JDBC().login(username, password); if (login){ System.out.println("验证通过,登录成功"); }else { System.out.println("登录失败,请检查账户或密码"); } } public boolean login (String username, String password) { if (username == null || password == null){ return false; } Connection conn = null; ResultSet rs = null; Statement stat = null; try { conn = JDBClogin.getConnection(); String sql = "select * from login where username = '" +username +"' and password = '"+password +"'"; stat = conn.createStatement(); rs = stat.executeQuery(sql); return rs.next(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBClogin.close(rs,stat,conn); } return false; } }