数据库驱动

10、JDBC

10.1、数据库驱动

驱动：声卡，显卡，数据库

10.2、JDBC

在这里插入图片描述

10.3、第一个JDBC程序

创建测试数据库

1、创建一个普通项目

CREATE DATABASE `jdbcstudy`CREATE TABLE  users
(
`id` INT (4) PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`birthday` DATE
);INSERT INTO `users` (`id`,`name`,`password`,`birthday`) 
VALUES ('1','张三','123456','1999-02-02'),('2','李四','1234556','1999-08-02'),('3','王五','1288556','1998-02-15')
,('4','赵六','752156','1999-12-12')

2.导入数据库驱动

package JDBC01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class test01 {	public static void main(String[] args) throws ClassNotFoundException, SQLException {//1.加载驱动Class.forName("com.mysql.jdbc.Driver");//固定//2.连接用户信息和urlString url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";String username ="root";String password="ehero000921";//3.连接成功，数据库对象 ConnectionConnection connection=DriverManager.getConnection(url, username, password);//4.执行sql对象Statement statement  =connection.createStatement();//5.执行sql的对象去执行sqlString sql="select * from users";ResultSet rs=statement.executeQuery(sql);while (rs.next()){System.out.print("id="+rs.getObject("id")+" ");System.out.print("name="+rs.getObject("name")+" ");System.out.print("password="+rs.getObject("password")+" ");System.out.println("birthday="+rs.getObject("birthday"));}//6.释放连接rs.close();statement.close();connection.close();}
}

步骤总结：

1.加载驱动

2.连接数据库DriverManager

3.获取执行sql的对象Statement

4.获的返回的结果集

5.释放连接

DriverManager

Class.forName("com.mysql.jdbc.Driver");
//固定写法

URL

String  url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"//mysql默认3306//协议://主机地址:端口号/数据库名？参数1&参数2&参数3

Statement 执行SQL的对象 PrepareStatement 执行的SQL的对象

String sql="sekect * from users";
statement.executeQuery();//查询
statement.execute();//执行任何SQL
statement.executeUpdate();//更新，插入，删除

ResultSet 查询的结果集：封装了所有的查询结果

获取定的数据类型

resultset.getint();
resultset.getobject();
resultset.getDate();
...//指针
resultset.beforFirst();//移动到第一个
resultset.afterLast();//移动到自动一个
resultset.absolute(row)//移动到指定类

10.4、statement对象

配置类

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=ehero000921
##最好写在src目录文件下，注意看写在什么地方

工具类

package JDBC01;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class jdbcutils {private static String driver=null;private static String url=null;private static String username=null;private static String password=null;static{try{
//			InputStream in=jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");InputStream in=jdbcutils.class.getClassLoader().getResourceAsStream("JDBC01/db.properties");//这样就好了 Properties properties=new Properties();properties.load(in);driver =properties.getProperty("driver");url =properties.getProperty("url");username =properties.getProperty("username");password =properties.getProperty("password");Class.forName(driver);//驱动只加载一次}catch(IOException e){e.printStackTrace();}catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//获取连接public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url,username,password);}//释放连接资源public static void release(Connection conn,Statement st, ResultSet rs) throws SQLException{if(rs!=null){rs.close();}if(st!=null){st.close();}if(conn!=null){conn.close();}}}

代码：

package JDBC01;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class testinsert {public static void main(String[] args) throws Exception {Connection conn =null;Statement st=null;ResultSet rs=null;try {conn= jdbcutils.getConnection();st=conn.createStatement();String sql="INSERT INTO `users`  (`id`,`name`,`password`,`birthday`)"+ " VALUES ('9','大傻逼2','55201314','2000-12-11') ";int i=st.executeUpdate(sql);if(i>0){System.out.println("插入成功!");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {jdbcutils.release(conn, st, rs);}}
}
//只需要改sql

sql注入问题

sql存在漏洞，会导致被拼接

package JDBC01;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class sql {public static void main(String[] args) {sql.login(" 'or '1=1", "'or '1=1");}public static void login(String username,String password){Connection conn=null;Statement sta=null;ResultSet re=null;try {conn= jdbcutils.getConnection();sta=conn.createStatement();String sql="SELECT * FROM `users`  WHERE `name`='"+username+"' AND `password`='"+password+"'";re=sta.executeQuery(sql);while(re.next()){System.out.print(re.getString("name")+"  ");System.out.println(re.getString("password"));}} catch (SQLException e) {e.printStackTrace();}}
}

10.5、PreparedStatement

增加

package JDBC01;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;import com.mysql.fabric.xmlrpc.base.Data;public class test02 {public static void main(String[] args) {Connection con=null;PreparedStatement pst=null;try {con=jdbcutils.getConnection();String sql ="insert into users(id,name,password,birthday) values(?,?,?,?)";pst=con.prepareStatement(sql);pst.setInt(1, 7);pst.setString(2, "蠢货");pst.setString(3, "5201314888");pst.setDate(4, new java.sql.Date(new Date().getTime()) );int i=pst.executeUpdate();if(i==1)System.out.println("插进去了");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {if(pst!=null)try {pst.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(con!=null)try {con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

删除

package JDBC01;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class test03 {public static void main(String[] args) {Connection con=null;PreparedStatement pst=null;try {con=jdbcutils.getConnection();String sql="delete from users where id=? ";pst=con.prepareStatement(sql);pst.setInt(1, 7);int i=pst.executeUpdate();if(i==1)System.out.println("删了");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
}

改变

package JDBC01;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class test04 {public static void main(String[] args) {Connection con=null;PreparedStatement pst=null;try {con=jdbcutils.getConnection();String sql="update users set `name`='杂种' where `id`=?";pst=con.prepareStatement(sql);pst.setInt(1, 5);int i=pst.executeUpdate();if(i==1)System.out.println("成功");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
}

删除

package JDBC01;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;import com.mysql.fabric.xmlrpc.base.Data;public class test05 {public static void main(String[] args) {Connection con=null;PreparedStatement pst=null;ResultSet rs=null;try {con=jdbcutils.getConnection();String sql="select * from `users` where id=?";pst=con.prepareStatement(sql);pst.setInt(1, 2);rs=pst.executeQuery();while(rs.next()){System.out.println(rs.getInt("id"));System.out.println(rs.getString("name"));System.out.println(rs.getString("password"));System.out.println(rs.getString("birthday"));
//				System.out.println(rs.getDate(new java.sql.Date(new Date().getTime())));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
}