1.JDBC:一套专门用于java程序操作数据库的接口。
发送条件:连接Mysql数据库:数据库主机,端口,数据库用户名,密码,连接的数据库
2.JDBC核心API:Driver接口:数据库驱动程序的接口,所有具体数据库厂商需要驱动的程序均需要实现此接口。
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DriverDm {
private static String url="jdbc:mysql://localhost:3306/day20";
// jdbc协议:数据库协议://主机地址:数据库端口号/连接的数据库名称
private static String user="root";
private static String password="root";
public static void main(String[] args) throws Exception {
//获取数据库连接对象三个方法
conn1();
conn2();
conn3();
}
//1.直接连接
public static void conn1() throws SQLException{
//创建驱动类对象Driver
Driver driver=new com.mysql.jdbc.Driver();
//创建一个properties集合
Properties prop=new Properties();
prop.setProperty("user", user);
prop.setProperty("password", password);
//获取java与数据库的连接
Connection conn=driver.connect(url, prop);
System.out.println(conn);//打印不为空,说明连接成功
}
//2.使用驱动管理类,获取驱动并建立连接
public static void conn2() throws SQLException{
//注册驱动
Driver driver=new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
//获取连接
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//常用方法
public static void conn3() throws Exception{
//mysql驱动程序的Driver实现类会自动完成注册功能,因此只需将Driver实现类加载到内存中
Class.forName("com.mysql.jdbc.Driver");
//获取java数据库连接对象
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//可以同时建立多个数据库连接对象
}
2.Connection接口:与具体的数据库的连接对象
Statement st=conn.createStatement();//创建静态sql语句对象
PreparedStatement pps=conn.prepareStatement(null);//创建预编译sql语句对象
CallableStatement cst=conn.prepareCall(null);//创建存储过程sql语句对象
3.Statement接口:用于执行静态sql语句
int i=st.executeUpdate("sql语句");
/*可以执行DDL语句(create/alter/drop)
* DML语句(insert/update/delete)
*/
ResultSet rs=st.executeQuery("sql语句");
//执行查询操作语句DQL:select
4.preparedstatement接口:用于执行预编译操作语句/是Statement的子接口
int j=pps.executeUpdate("sql语句");//执行更新操作
ResultSet rs2=pps.executeQuery("sql语句");//执行查询操作
5.Callablestatement接口:用于执行存储过程的接口/是Statement的子接口
7.Statement对象执行DDL操作exp:
8.执行DML操作:
9.执行DQL查询操作
10.JDBC工具类抽取:
11.PreparedStatement对象执行SQL操作
ResultSet rs3=cst.executeQuery("sql语句");//执行存储操作
6.ResultSet接口:结果集对象,搜索所有查询的结果,用该对象进行遍历
while(rs.next()){//判断下一行是否有数据
System.out.println(rs.getInt(1));//输出获取到的字段数据
}
7.Statement对象执行DDL操作exp:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DDLDemo {
private static String url="jdbc:mysql://localhost:3306/day20";
private static String user="root";
private static String password="root";
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url, user, password);
st=conn.createStatement();
String sql="CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));";
String sql2="ALTER TABLE student ADD COLUMN age INT;";
String sql3="DROP TABLE student;";
int count=st.executeUpdate(sql);
int count2=st.executeUpdate(sql2);
int count3=st.executeUpdate(sql3);
System.out.println(count);
System.out.println(count2);
System.out.println(count3);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭资源
finally{
if(st!=null){//防止没有输入程序时资源关闭
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//三个操作均影响0行
}
}
8.执行DML操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DMLDemo {
private static String url="jdbc:mysql://localhost:3306/day20";
private static String user="root";
private static String password="root";
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
String name="jack";
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url, user, password);
st=conn.createStatement();
//添加数据
String sql="INSERT INTO student(NAME) VALUES ('tom');";
//更新数据。。还可以这样
String sql2="UPDATE student SET NAME ='"+name+"'WHERE id =1;";
//删除数据
String sql3="DELETE FROM student WHERE id=1;";
int c1=st.executeUpdate(sql);
System.out.println(c1);
int c2=st.executeUpdate(sql2);
System.out.println(c2);
int c3=st.executeUpdate(sql3);
System.out.println(c3);
} catch (Exception e) {
// TODO: handle exception
}finally{
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//若成功执行,则均影响一行
}
}
}
9.执行DQL查询操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class EQLDemo {
private static String url="jdbc:mysql://localhost:3306/day20";
private static String user="root";
private static String password="root";
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url, user, password);
st=conn.createStatement();
String sql1="SELECT *FROM student;";
rs=st.executeQuery(sql1);
while(rs.next()){
int id=rs.getInt("id");
String name =rs.getString("name");
System.out.println(id+"\t"+name);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
10.JDBC工具类抽取:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
private static String url="jdbc:mysql://localhost:3306/day20";
private static String user="root";
private static String password="root";
private static String forname="com.mysql.jdbc.Driver";
static{
try {
Class.forName(forname);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnectioin(){
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException();
}
}
public static void close(ResultSet rs,Statement st,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
11.PreparedStatement对象执行SQL操作
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.edu_03.JDBCUtil;
//插入操作
public class Demo1 {
public static void main(String[] args) {
//test1();
//test2();
//test3();
test4();
}
public static void test1(){
Connection conn=null;
PreparedStatement ppst=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="insert into student (name) values(?);";
ppst=conn.prepareStatement(sql);
ppst.setString(1, "faker");
int count=ppst.executeUpdate();
System.out.println(count);
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null,ppst,conn);
}
}
//修改操作
public static void test2(){
Connection conn=null;
PreparedStatement ppst=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="UPDATE student SET NAME=? WHERE id=?;";
ppst=conn.prepareStatement(sql);
ppst.setString(1, "saber");
ppst.setInt(2, 3);
int count =ppst.executeUpdate();
System.out.println(count);
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null, ppst, conn);
}
}
//删除数据
public static void test3(){
Connection conn=null;
PreparedStatement ppst=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="DELETE FROM student WHERE id=?;";
ppst=conn.prepareStatement(sql);
ppst.setObject(1, 4);
int count =ppst.executeUpdate();
System.out.println(count);
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null, ppst, conn);
}
}
//查询数据
public static void test4(){
Connection conn=null;
PreparedStatement ppst=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="SELECT *FROM student;";
ppst=conn.prepareStatement(sql);
ResultSet rs=ppst.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name"));
}
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null, ppst, conn);
}
}
}
12.statement和Preparedstatement的区别:
A:语法结构不同:Statement执行静态sql语句,且sql可以拼接。
Preparedstatement可以执行预编译sql语句,在语句中使用?来赋值。
B:原理不同:Statement不能进行sql缓存。
Preparedstatement可以进行sql缓存,执行效率比前者快。
C:安全性不同:Statement存在sql注入的风险
Preparedstatement可以避免sql注入
-- 创建user表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
PASSWORD VARCHAR(20)
);
-- 向表中插入用户数据
INSERT INTO USER(NAME,PASSWORD) VALUES('james','123456');
INSERT INTO USER(NAME,PASSWORD) VALUES('mayun','123456');
-- sql注入:在查询表的时候先添加单引号分隔字符,导致后边字符失效,然后在
-- 传入 OR 1=1改变逻辑关系,就会显示全部数据,导致用户数据泄露
-- 所以用PreparedStatement可以先执行预编译,规定好数据格式,防止sql注入。
SELECT * FROM USER WHERE NAME='james' OR 1=1-- ' AND PASSWORD='123456';
13.CallableStatement执行存储过程:
CREATE PROCEDURE pro_testwhile(IN num INT,OUT SUM INT)
BEGIN
DECLARE i INT DEFAULT 1; -- 定义局部变量
DECLARE result INT DEFAULT 0;
WHILE i<num DO
SET result=result+i;
SET i=i+1;
END WHILE;
SET SUM=result;
END $
DELIMITER $ -- 输入id查看员工信息
CREATE PROCEDURE pro_testid(IN ide INT)
BEGIN
SELECT * FROM employee WHERE id=ide;
END $
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import com.edu_03.JDBCUtil;
public class InputDemo {
public static void main(String[] args) {
//test1();
test2();
}
//执行带有输入参数的存储过程
public static void test1(){
Connection conn=null;
CallableStatement stmt=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="CALL pro_testid(2);";
stmt=conn.prepareCall(sql);
ResultSet rs=stmt.executeQuery();// 执行存储过程必须使用exeuteQuery
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name"));
}
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null, stmt, conn);
}
}
//执行带有输出参数的存储过程
public static void test2(){
Connection conn=null;
CallableStatement stmt=null;
try {
conn=JDBCUtil.getConnectioin();
String sql="CALL pro_testwhile(?,?);";
stmt=conn.prepareCall(sql);
stmt.setObject(1, 153);
stmt.registerOutParameter(2,java.sql.Types.INTEGER);
//注册一个输出参数,第一个参数是该参数在存储过程中的位置,第二个参数是out参数的数据库类型
stmt.executeQuery();
//获取存储过程的返回值
String s=stmt.getString(2);//该数据和预编译中的参数位置保持一致
System.out.println(s);
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.close(null, stmt, conn);
}
}
}
14.优化jdbc工具类
import java.io.FileInputStream;
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 EXJDBCUtil {
private static String url;
private static String user;
private static String password;
private static String className;
static{//静态代码块注册驱动
//给成员变量赋值,将文件中的键值对加载到集合中
try {
Properties prop=new Properties();
InputStream is=new FileInputStream("db.Porperties");
prop.load(is);
url=prop.getProperty(url);
user=prop.getProperty(user);
password=prop.getProperty(password);
className=prop.getProperty(className);
System.out.println(url);
System.out.println(user);
System.out.println(password);
System.out.println(className);
Class.forName(className);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public static Connection getconnention(){
try {
Connection conn=DriverManager.getConnection(url, user, password);
return conn;
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException();
}
}
//释放资源
public static void close(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}