JDBC(Java DataBase Connectivity)java数据库连接
● 是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
● 有了JDBC,java开发人员只需要编写一次程序,就可以访问不同的数据库.
对于JDBC API:
供程序员调用的接口与类,集成在java.sql包中
DriverManager类作用:管理各种不同的jDBC驱动
Connection 接口 与特定数据库的连接
Statement 接口 执行sql
PreparedStatement接口 执行sql
ResultSet接口 接收查询结果
第一步:
需要初始化驱动程序,这样就可以打开与数据库的通信信道。
Class.forName(“com.mysql.cj.jdbc.Driver”); //反射实现 or DriverManager.registerDriver(new Driver());
第二步:
建立与数据库连接:
这需要使用DriverManager.getConnection()方法来创建一个
Connection对象,它代表一个物理连接的数据库.
Connection con = DriverManager.getConnection(URL,USER,PASS); URL:jdbc:mysql://ip(127.0.0.1):端口(3306)/数据库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC USER:本地数据库用户名(root) PASS:本地数据库密码
第三步:
获得Satement执行sql语句
Statement st = connection.createStatement();
Satement中的方法: Int executeUpdate(String sql) 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(String sql); 用于执行查询语句 返回一个ResultSet 集合
示例 :
public static void main(String[] args) { try { //加载mysql驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //连接数据库 Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); // System.out.println(connection); //发送SQL到数据库 Statement st= connection.createStatement(); st.executeUpdate("insert into course (course)values ('php')");//向数据库中插入数据 //中断传输 st.close(); connection.close(); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } }
接下来我们开始利用IDEA进行对数据库表中数据的更改
从最基本的利用数据库添加数据开始
public class JDBCDemo1 { public static void main(String[] args) throws SQLException { JDBCDemo1 jdbcDemo1=new JDBCDemo1(); jdbcDemo1.student("tom","男","2020-2-2"); } private void student(String name,String sex,String birthday) throws SQLException { Connection connection=null; Statement st=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); st=connection.createStatement(); int row = st.executeUpdate("INSERT INTO t_student(NAME,sex,birthday,reg_time)" + " VALUES('"+name+"','"+sex+"','"+birthday+"',now())"); System.out.println(row); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); st.close(); } } }
以上的方法适用于初学, 不便于向数据库中添加多条数据,通过自定义Student方法,可以提高添加的方法和效率
获得PrepareStatement执行sql语句
● 在sql语句中参数位置使用占位符,使用setXX方法向sql中设置参数
PrepareStatement ps = connection.prepareStatement(sql);
PrepareStatement中的方法:
Int executeUpdate() 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(); 用于执行查询语句 返回一个ResultSet 集合
public class Student { private int id; private String name; private String sex; private Date birthday; private Date reg_time; private int phone; private int height; private int weight; private int score; 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 String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday(Timestamp birthday) { return this.birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public Date getReg_time(Timestamp reg_time) { return this.reg_time; } public void setReg_time(Date reg_time) { this.reg_time = reg_time; } public int getPhone() { return phone; } public void setPhone(int phone) { this.phone = phone; } public int getHeight(int height) { return this.height; } public void setHeight(int height) { this.height = height; } public int getWeight(int weight) { return this.weight; } public void setWeight(int weight) { this.weight = weight; } public int getScore(int score) { return this.score; } public void setScore(int score) { this.score = score; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + ", reg_time=" + reg_time + ", phone=" + phone + ", height=" + height + ", weight=" + weight + ", score=" + score + '}'; } }
public class JDBCDemo5 { public static void main(String[] args) throws SQLException { JDBCDemo5 jdbcDemo5=new JDBCDemo5(); jdbcDemo5.student("tom","男","2000-3-3","2000-3-3"); } private void student(String name, String sex,Object birthday,Object reg_time) throws SQLException { Connection connection=null; PreparedStatement pt=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); pt=connection.prepareStatement("insert into t_student(name,sex,birthday,reg_time)values(?,?,?,?)");//?表示占位符, pt.setString(1,name); pt.setString(2,sex); pt.setObject(3,birthday); pt.setObject(4,reg_time); pt.executeUpdate(); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { if(connection!=null){ connection.close(); } if(pt!=null){ pt.close(); } } } }
其中PreparedStatement的第一次执行消耗是很高的. 它的性能体现在后面的重复执行. 使用PreparedStatement的方式来执行一个针对数据库表的查询. JDBC驱动会发送一个网络请求到数据解析和优化这个查询. 而执行时会产生另一个网络请求. 在JDBC驱动中,减少网络通讯是最终的目的. 如果我的程序在运行期间只需要一次请求, 那么就使用Statement. 对于Statement, 同一个查询只会产生一次网络到数据库的通讯. 这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次,最重要的一点是极大地提高了安全性. 防止sql注入
进行删除操作
public class JDBCDemo2 { public static void main(String[] args) throws SQLException { JDBCDemo2 jdbcDemo2=new JDBCDemo2(); jdbcDemo2.student(1); } private void student(int id) throws SQLException { Connection connection=null; Statement st=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); st=connection.createStatement(); int row = st.executeUpdate("delete from number where Dept_id="+id); int row1=st.executeUpdate("delete from dept where id="+id); System.out.println(row); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); st.close(); } } }
注意:在进行删除操作时 , 如果有多表关联,需要优先删除与其相关联的从表,否则无法进行删除操作
利用PreparedStatement方法执行操作时
public class JDBCDemo6 { public static void main(String[] args) throws SQLException { JDBCDemo6 jdbcDemo5=new JDBCDemo6(); jdbcDemo5.student(10); } private void student(int id) throws SQLException { Connection connection=null; PreparedStatement pt=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); pt=connection.prepareStatement("delete from t_student where id=?"); pt.setObject(1,id); pt.executeUpdate(); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace();} finally { if(connection!=null){ connection.close(); } if(pt!=null){ pt.close(); } } } }
进行数据库消息的更改:
public class JDBCDemo4 { public static void main(String[] args) throws SQLException { JDBCDemo4 jdbcDemo3=new JDBCDemo4(); jdbcDemo3.student(8,"jy","女","2000-12-13"); } private void student(int id,String name,String sex,String birthday) throws SQLException { Connection connection=null; Statement st=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); st=connection.createStatement(); int row = st.executeUpdate("update t_student set name='"+name+"',sex='"+sex+"',birthday='"+birthday+"'where id="+id); System.out.println(row); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); st.close(); } } }
利用PreparedStatement方法执行删除操作时:
public class JDBCDemo7 { public static void main(String[] args) throws SQLException { JDBCDemo7 jdbcDemo5=new JDBCDemo7(); jdbcDemo5.student("1 or 1=1"); } private void student(String id) throws SQLException { Connection connection=null; PreparedStatement pt=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); pt=connection.prepareStatement("delete from t_area where id=?"); pt.setObject(1,id); pt.executeUpdate(); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace();} finally { if(connection!=null){ connection.close(); } if(pt!=null){ pt.close(); } } } }
public class JDBCDemo7b { public static void main(String[] args) throws SQLException { JDBCDemo7b jdbcDemo2=new JDBCDemo7b(); jdbcDemo2.student("1 or 1=1"); } private void student(String id) throws SQLException { Connection connection=null; Statement st=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); st=connection.createStatement(); int row = st.executeUpdate( "delete from t_area where id = 1 or 1 = 1"); System.out.println(row); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); st.close(); } } }
其中JDBCDemo7的线程,他会进行检测SQL攻击,在通过set方法在设置值时,会进行检测,如果传入其他关键字时,就会显示异常
在JDBCDemo7b中他无法对传入的值进行检验,在满足1=1的条件后会执行对数据库的清除操作对数据库整体进行清空,执行完成后会返回 0.
利用PreparedStatement方法执行对数据库查找操作时
public class JDBCDemo8 { public static void main(String[] args) throws SQLException { JDBCDemo8 jdbcDemo1=new JDBCDemo8(); jdbcDemo1.student(8); } private void student(int id) throws SQLException { Connection connection=null; //连接数据库 PreparedStatement pt=null; Student student=new Student(); try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); pt=connection.prepareStatement("\n" + "SELECT\n" + " id,\n" + " NAME,\n" + " sex,\n" + " birthday,\n" + " reg_time,\n" + " phone,\n" + " height,\n" + " weight,\n" + " score\n" + "FROM\n" + " t_student\n" + "WHERE id = ?\n"); pt.setInt(1,id); ResultSet res=pt.executeQuery(); while(res.next()){ student.setId(res.getInt("id")); student.setName(res.getString("name")); student.setSex(res.getString("sex")); student.getBirthday(res.getTimestamp("birthday")); student.getReg_time(res.getTimestamp("reg_time")); student.getHeight(res.getInt("height")); student.getWeight(res.getInt("weight")); student.getScore(res.getInt("score")); } System.out.println(student); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); pt.close(); } } }
如果要同时查找多条数据时,我们可以利用集合进行查找:
public class JDBCDemo9 { public static void main(String[] args) throws SQLException { JDBCDemo9 jdbcDemo1=new JDBCDemo9(); jdbcDemo1.student("男"); } private ArrayList<Student>student(String sex) throws SQLException { Connection connection=null; //连接数据库 PreparedStatement pt=null; ArrayList<Student>list=new ArrayList(); Student student=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root"); pt=connection.prepareStatement("\n" + "SELECT\n" + " id,\n" + " NAME,\n" + " sex,\n" + " birthday,\n" + " reg_time,\n" + " phone,\n" + " height,\n" + " weight,\n" + " score\n" + "FROM\n" + " t_student\n" + "WHERE sex = ?\n"); pt.setString(1,sex); ResultSet res=pt.executeQuery(); while(res.next()){ student=new Student(); student.setId(res.getInt("id")); student.setName(res.getString("name")); student.setSex(res.getString("sex")); student.getBirthday(res.getTimestamp("birthday")); student.getReg_time(res.getTimestamp("reg_time")); student.getHeight(res.getInt("height")); student.getWeight(res.getInt("weight")); student.getScore(res.getInt("score")); list.add(student); } System.out.println(list); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } finally { connection.close(); pt.close(); } return list; } }
使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值