java JDBC的使用
- JDBC(Java Database Connectivity) 是基于JAVA语言访问数据库的一种技能
- JDBC是一种用于执行SQl语句的Java API,可以为多种关系型数据库提供统一访问,它由一组用Java语言编写的类和接口组成.JDBC提供了一种基准,据此可以构建高级工具和接口,使数据库开发人员可以或许编写数据库应用步调,同时JDBC也是个商标名
- JDBC包罗一套JDBC的API和一套步调员和数据库厂商必须去遵守的规范(java.sql包:提供访问数据库的根本功能、javax.sql包:提供拓展的功能)
- JDBC可以做什么?(毗连到数据库、在java app中执行sql下令、处置惩罚效果)
jdbc是java访问控制数据库内里数据的一套标准接口,我们常说java是面向对象编程,实际上就是面向接口编程
职责:
1、java:客户端:吸收数据、拼接sql、分析效果、返回效果给browser
2、db:服务器: 吸收sql、分析处置惩罚、返回效果给java
(我们毗连利用数据库,是不停需要举行创建毗连断开毗连利用的.这里会涉及到我们的数据库毗连池.)
面向接口编程:
面向接口编程: java.sql.*
- java.sql.Driver:–>驱动
- java.sql.Connection—>毗连
- java.sql.Statement —>静态处置惩罚块
- java.sql.PreparedStatement -->预处置惩罚块
- java.sql.ResultSet —>效果集
- java.sql.ResultSetMeteData —>效果集元数据
常用数据库毗连方式:
MySQL:
- String Driver=“com.mysql.jdbc.Driver”;//驱动步调
- String URL=“jdbc:mysql://localhost:3306/db_name”;//创建的URL. db_name为数据库名
- String Username=“username”; //用户名
- String Password=“password”; //暗码
- Class.forName(Driver);
- Connection con=DriverManager.getConnection(URL,UserName,Password)
Oracle:
- String Driver=“oracle.jdbc.driver.OracleDriver”; //驱动步调
- String URL=“jdbc:oracle:thin:@localhost:1521:orcl”;//创建的URL,orcl为数据库的SID
- String Username=“username”; //用户名
- String Password=“password”; //暗码
- Class.forName(Driver);
- Connection con=DriverManager.getConnection(URL,UserName,Password);
PostgreSQL:
- String Driver=“org.postgresql.Driver”; //驱动步调
- String URL=“jdbc:postgresql://localhost/db_name”;//创建的URL. db_name为数据库名
- String Username=“username”; //用户名
- String Password=“password”; //暗码
- Class.forName(Driver);
- Connection con=DriverManager.getConnection(URL,UserName,Password)
DB2:
- String Driver=“com.jbm.dbjdbc.app.DBDriver”; //毗连具有DB2客户端的Provide实例
- String Driver=“com.jbm.dbjdbc.net.DBDriver”; //毗连不具有DB2客户端的Provide实例
- String URL=“jdbc:db2://localhost:5000/db_name”;//创建的URL. db_name为数据库名
- String Username=“username”; //用户名
- String Password=“password”; //暗码
- Class.forName(Driver);
Microsoft SQL Server:
- String Driver=“com.microsoft.sqlserver.jdbc.SQLServerDriver”;//毗连SQL数据库的驱动
- String URL=“jdbc.sqlserver://localhost:1433;DatabaseName=db_name”;//db_name为数据库名
- String Username=“username”; //用户名
- String Password=“password”; //暗码
- Class.forName(Driver).new Instance();//加载数据可驱动
- public class JDBCTest { public static void main(String[] args) { String Driver="com.mysql.jdbc.Driver"; String URL="jdbc:mysql://localhost:3306/test"; String UserName="root"; String Password="ABCDEFG";//数据库暗码 try { //1.加载毗连jdbc的驱动（Class反射，这种写法实际上是有一个Class范例的返回值的实际对象。方便我们利用该累的详细细节。) /** * 当执行了当前代码后，会返回一个class对象，在此对象的创建过程中，会调用详细类的静态代码块 */ Class.forName(Driver); //2.创建毗连 //第一步中已经将driver对象注册到了drivermanager中，所以此时可以直接通过DriverManager来获取数据库的毗连 /** * 需要输入数据库的参数举行毗连 * url：数据库的所在 * username：用户名 * password：暗码 */ Connection connection = DriverManager.getConnection(URL,UserName,Password); System.out.println(connection);//3.测试毗连是否乐成 //4。界说sql语句 //只要填写正常执行的sql即可 String sql="select * from Pay;"; //5.准备静态处置惩罚块对象，将sql语句放置到静态处置惩罚块中,明白为存放sql语句的对象 /** * 在执行sql语句的过程中，需要一个对象来存放sql语句，将对象举行执行的时候调用的是数据库的服务，数据库会从当前对象中拿到对应的sql语句举行执行 */ Statement statement = connection.createStatement(); //6.执行sql语句,返回对象时效果聚集 /** * 将效果放到resultSet，是返回效果的一个聚集，需要颠末一个循环迭代，才气获取此中的每一条记载 * statement在执行的时候可以选择三种方式： * 1、execute 任何语句都可以执行 * 2、executeQuery 只能执行查询语句 * 3、executeUpdate 只能执行DML语句 */ ResultSet resultSet = statement.executeQuery(sql); //7.循环处置惩罚 /** * 使用while循环，有两种获取详细值的方式（1.通过索引编号获取，从1开始。 2.通过列名获取，推荐通过列名获取） */ while (resultSet.next()){ int anInt = resultSet.getInt(1);//索引从1开始 System.out.println(anInt); String PaymentMethod = resultSet.getString("PaymentMethod"); System.out.println(PaymentMethod); System.out.println("-----------------------------------"); } //8.关闭毗连(不关闭的话会非常占内存) statement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } }}
- public class CreateDataBase { public static void main(String[] args) throws ClassNotFoundException, SQLException { String Driver="com.mysql.jdbc.Driver"; String URL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8"; String User="root"; String Password="AAAAA"; Class.forName(Driver); Connection connection = DriverManager.getConnection(URL,User,Password); String sql= "create database if not exists testNewDataBase;"; Statement statement = connection.createStatement(); int update = statement.executeUpdate(sql); int update1 = statement.executeUpdate("use testNewDataBase;"); System.out.println(update+"------------"+update1); statement.close(); connection.close(); }}
- /** * 从api可看出当执行DDL语句的时候，不会反回对应的效果 */public class CreateTable { public static void main(String[] args) throws ClassNotFoundException, SQLException { String Driver="com.mysql.jdbc.Driver"; String URL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8"; String User="root"; String Password="AAAAAAAA"; Class.forName(Driver); Connection connection = DriverManager.getConnection(URL, User, Password); Statement statement = connection.createStatement(); statement.executeUpdate("use testNewDataBase;"); String sql="create table temp(id int(10) primary key,name varchar(10));"; boolean execute = statement.execute(sql); System.out.println(execute); statement.close(); connection.close(); }}
在此之前我们先简单画下ER图
将代码分实体类,工具类…等举行分开存放,为什么分开存放?在我们的实际开发过程中,大概遇到成百上千个java文件的大项目,如果代码挤在一起,就会显得很乱,不管对于自己照旧对于别人,可读性都是非常差的.
所以我们可以根据差别的功能,差别的特点,将代码分别到差别的包下面,这样可以方便我们后期的查找和修改.
1.创建Util包,包内里创建DBUtil类,该类用来作为一个工具类,创建毗连的方法,关闭毗连的方法主要在该类实现
- package com.jdbcUtil.Util;import java.sql.*;/** * 该类为步调工具类 */public class DBUtil { public static String driver="com.mysql.jdbc.Driver"; public static String URL="jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8"; public static String UserName="root"; public static String Password="Azog095874"; public DBUtil() { } /** * 创建对象 * @param driver * @param URL * @param UserName * @param Password */ public DBUtil(String driver, String URL, String UserName, String Password) { setDriver(driver); setURL(URL); setUserName(UserName); setPassword(Password); } static { try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取数据库毗连 * @return返回毗连对象 */ public static Connection getConnection(){ try { Connection connection = DriverManager.getConnection(URL,UserName,Password); return connection; } catch (SQLException e) { e.printStackTrace(); } return null; } /** * 重载多次closeConnection方法，方便多种情况下的调用 * @param connection * @param statement * @param resultSet */ public static void closeConnection(Connection connection, Statement statement, 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(); } } } public static void closeConnection(Connection connection, Statement statement){ if (statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void closeConnection(Connection connection){ if (connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static String getDriver() { return driver; } public static void setDriver(String driver) { DBUtil.driver = driver; } public static String getURL() { return URL; } public static void setURL(String URL) { DBUtil.URL = URL; } public static String getUserName() { return UserName; } public static void setUserName(String userName) { UserName = userName; } public static String getPassword() { return Password; } public static void setPassword(String password) { Password = password; }}
- package com.jdbcUtil.Entity;import java.util.Date;/** * 实体类 */public class Emp { private Integer empnum; private String empname; private String job; private Integer mrg; private String hiredate; private double sal; private double comm; private Integer deptnum; public Emp(){} public Emp(Integer empnum, String empname, String job, Integer mrg, String hiredate, double sal, double comm, Integer deptnum) { this.empnum = empnum; this.empname = empname; this.job = job; this.mrg = mrg; this.hiredate = hiredate; this.sal = sal; this.comm = comm; this.deptnum = deptnum; } public Integer getEmpnum() { return empnum; } public void setEmpnum(Integer empnum) { this.empnum = empnum; } public String getEmpname() { return empname; } public void setEmpname(String enama) { this.empname = enama; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public Integer getMrg() { return mrg; } public void setMrg(Integer mrg) { this.mrg = mrg; } public String getHiredate() { return hiredate; } public void setHiredate(String hiredate) { this.hiredate = hiredate; } public double getSal() { return sal; } public void setSal(double sal) { this.sal = sal; } public double getComm() { return comm; } public void setComm(double comm) { this.comm = comm; } public Integer getDeptnum() { return deptnum; } public void setDeptnum(Integer deptnum) { this.deptnum = deptnum; } @Override public String toString() { return "Emp{" + "empnum=" + empnum + ", enama='" + empname + '\'' + ", job='" + job + '\'' + ", mrg=" + mrg + ", hiredate=" + hiredate + ", sal=" + sal + ", comm=" + comm + ", deptnum=" + deptnum + '}'; }}
- package com.jdbcUtil.Action;import com.jdbcUtil.Entity.Emp;import com.jdbcUtil.Util.DBUtil;public interface FormalActions { //创建表格 //插入数据 public void insert(Emp emp); //删除数据 public void delete(Emp emp); //修改数据 public void update(Emp emp); //查询数据 public Emp getEmpByEmpnum(Integer empNum); public Emp getEmpByEmpname(String name);}
- package com.jdbcUtil.Action;import com.jdbcUtil.Entity.Emp;import com.jdbcUtil.Util.DBUtil;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.Date;import java.util.logging.SimpleFormatter;public class FormalActionImpl implements FormalActions { /** * 当插入数据的时候要注意属性范例的匹配 * 1、Date * 2、Stream在拼接sql的时候必须加单引号 * 3、 * @param emp */ @Override public void insert(Emp emp) { Connection connection = null; Statement statement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 statement = connection.createStatement(); //拼接sql语句 String sql = "insert into emp values("+emp.getEmpnum()+",'"+emp.getEmpname()+"','"+emp.getJob()+"',"+ emp.getMrg()+",date_format('"+emp.getHiredate()+"','%Y-%m-%d'),"+emp.getSal()+","+emp.getComm()+","+emp.getDeptnum()+")"; System.out.println(sql); //返回值表现受影响的行数 int i = statement.executeUpdate(sql); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement); } } @Override public void delete(Emp emp) { Connection connection = null; Statement statement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 statement = connection.createStatement(); //拼接sql语句 String sql = "delete from Emp where empname='"+emp.getEmpname()+"';"; System.out.println(sql); //返回值表现受影响的行数 int i = statement.executeUpdate(sql); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement); } } @Override public void update(Emp emp) { Connection connection = null; Statement statement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 statement = connection.createStatement(); //拼接sql语句 String sql = "update Emp set job='"+emp.getJob()+"'where empname='"+emp.getEmpname()+"';"; System.out.println(sql); //返回值表现受影响的行数 int i = statement.executeUpdate(sql); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement); } } @Override public Emp getEmpByEmpnum(Integer empNum) { Connection connection = null; Statement statement =null; Emp emp = null; ResultSet resultSet = null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 statement = connection.createStatement(); //拼接sql语句 String sql = "select * from Emp where empnum="+empNum+";"; System.out.println(sql); //返回值表现受影响的行数 resultSet = statement.executeQuery(sql); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); while (resultSet.next()){ emp = new Emp(resultSet.getInt("empnum"),resultSet.getString("empname"), resultSet.getString("job"),resultSet.getInt("mrg"), sdf.format(resultSet.getDate("hiredate")),resultSet.getDouble("sal"), resultSet.getDouble("comm"),resultSet.getInt("deptnum")); } return emp; } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement); } return emp; } @Override public Emp getEmpByEmpname(String name) { Connection connection = null; Statement statement =null; Emp emp = null; ResultSet resultSet =null; try { connection = DBUtil.getConnection();// connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 statement = connection.createStatement(); //拼接sql语句 String sql = "select * from Emp where empname="+name+";"; System.out.println(sql); //返回值表现受影响的行数 resultSet = statement.executeQuery(sql); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); while (resultSet.next()){ emp = new Emp(resultSet.getInt("empnum"),resultSet.getString("empname"), resultSet.getString("job"),resultSet.getInt("mrg"), sdf.format(resultSet.getDate("hiredate")),resultSet.getDouble("sal"), resultSet.getDouble("comm"),resultSet.getInt("deptnum")); } return emp; } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement,resultSet); } return emp; } public static void main(String[] args) { FormalActions formalActions = new FormalActionImpl(); Emp emp = new Emp(222,"zhangsan","dev",1111,"2019-08-07",1.005,1.002,111);// formalActions.insert(emp);// formalActions.delete(emp);// formalActions.update(emp);// Emp emp1 = formalActions.getEmpByEmpnum(111); Emp emp2 = formalActions.getEmpByEmpname("'lisi' or 1=1"); System.out.println(emp2); }}
这里我们要注意一点,在这个方法类的主函数内里,我们明显搜索的是lisi的内容,却出现了其他人的数据,只因我们在传输sql语句时添加了反面的 or 1=1
,就造成了sql注入,提取了全部数据.这种情况我们要注意,没准就把自己的饭碗给丢了.
我们接下来优化方法内里的代码
- package com.jdbcUtil.Action;import com.jdbcUtil.Entity.Emp;import com.jdbcUtil.Util.DBUtil;import java.sql.*;import java.text.ParseException;import java.text.SimpleDateFormat;public class FormalActionImpl3 implements FormalActions { /** * 当插入数据的时候要注意属性范例的匹配 * 1、Date * 2、Stream在拼接sql的时候必须加单引号 * 3、 * @param emp */ @Override public void insert(Emp emp) { Connection connection = null; PreparedStatement statement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); //拼接sql语句 String sql = "insert into emp values(?,?,?,?,?,?,?,?)"; statement = connection.prepareStatement(sql); System.out.println(sql); statement.setInt(1,emp.getEmpnum()); statement.setString(2,emp.getEmpname()); statement.setString(3,emp.getJob()); statement.setInt(4,emp.getMrg()); statement.setDate(5,Date.valueOf(emp.getHiredate())); statement.setDouble(6,emp.getSal()); statement.setDouble(7,emp.getComm()); statement.setInt(8,emp.getDeptnum()); //返回值表现受影响的行数 int i = statement.executeUpdate(); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.closeConnection(connection,statement); } } @Override public void delete(Emp emp) { Connection connection = null; PreparedStatement statement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 //拼接sql语句 String sql = "delete from Emp where empname=?"; statement = connection.prepareStatement(sql); statement.setString(1,emp.getEmpname()); System.out.println(sql); //返回值表现受影响的行数 int i = statement.executeUpdate(); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement); } } @Override public void update(Emp emp) { Connection connection = null; PreparedStatement preparedStatement =null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 //拼接sql语句 String sql = "update Emp set job= ? where empname= ?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,emp.getJob()); preparedStatement.setString(2,emp.getEmpname()); System.out.println(sql); //返回值表现受影响的行数 int i = preparedStatement.executeUpdate(); System.out.println("受影响的行数是："+i); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,preparedStatement); } } @Override public Emp getEmpByEmpnum(Integer empNum) { Connection connection = null; PreparedStatement statement =null; Emp emp = null; ResultSet resultSet = null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交 //拼接sql语句 String sql = "select * from Emp where empnum= ? "; statement = connection.prepareStatement(sql); statement.setInt(1,empNum); System.out.println(sql); //返回值表现受影响的行数 resultSet = statement.executeQuery(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); while (resultSet.next()){ emp = new Emp(resultSet.getInt("empnum"),resultSet.getString("empname"), resultSet.getString("job"),resultSet.getInt("mrg"), sdf.format(resultSet.getDate("hiredate")),resultSet.getDouble("sal"), resultSet.getDouble("comm"),resultSet.getInt("deptnum")); } return emp; } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,statement,resultSet); } return null; } @Override public Emp getEmpByEmpname(String name) { Connection connection = null;// Statement statement =null; Emp emp = null; ResultSet resultSet =null; PreparedStatement preparedStatement= null; try { connection = DBUtil.getConnection(); connection.setAutoCommit(true);//设置事务是否自动提交，true表现自动提交，false表现不是自动提交// statement = connection.createStatement(); //拼接sql语句 String sql = "select * from Emp where empname= ? "; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,name); System.out.println(sql); //返回值表现受影响的行数 resultSet = preparedStatement.executeQuery(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); while (resultSet.next()){ emp = new Emp(resultSet.getInt("empnum"),resultSet.getString("empname"), resultSet.getString("job"),resultSet.getInt("mrg"), sdf.format(resultSet.getDate("hiredate")),resultSet.getDouble("sal"), resultSet.getDouble("comm"),resultSet.getInt("deptnum")); }// return emp; } catch (SQLException e) { e.printStackTrace(); }finally { DBUtil.closeConnection(connection,preparedStatement,resultSet); } return emp; } public static void main(String[] args) { FormalActions formalActions = new FormalActionImpl3(); Emp emp = new Emp(222,"zhangsan","sales",1111,"2019-08-07",1.005,1.002,111); formalActions.insert(emp);// formalActions.delete(emp);// formalActions.update(emp);// Emp emp1 = formalActions.getEmpByEmpnum(111);// Emp emp2 = formalActions.getEmpByEmpname("lisi");// System.out.println(emp1); }}
Statement 换成了preparedStatement
这样就能是我们的代码制止这种预防不了sql注入的情况
批处置惩罚
在一些应用场景中,我们需要向数据库一下插入上百上千条条数据,但一直毗连和断开毗连,大概会出现毗连超时的情况,这时我们就可以使用批处置惩罚来举行
- public class batch { public static void main(String[] args) { insert(); } public static void insert(){ Connection connection = null; PreparedStatement preparedStatement =null; String sql="insert into emp(empnum,empname) values(?,?)"; try { connection = DBUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); for (int i=0;i com.oracle.database.jdbc ojdbc10 19.9.0.0
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
