数据库驱动
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.连接用户信息和url
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username ="root";
String password="ehero000921";
//3.连接成功，数据库对象 Connection
Connection connection=DriverManager.getConnection(url, username, password);
//4.执行sql对象
Statement statement =connection.createStatement();
//5.执行sql的对象去执行sql
String 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 block
e.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 block
e.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 block
e.printStackTrace();
}finally {
if(pst!=null)
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(con!=null)
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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 block
e.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 block
e.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 block
e.printStackTrace();
}
}
}
10.6、使用IDEA连接数据库
10.7、数据库连接池
数据库连接----执行完毕—释放
连接十分浪费资源
池化技术：准备一些预先的资源，过来就是连接准备好的
----开门----业务员：等待–服务—
常用连接数：10
最小连接数：10
最小连接数：100 业务最高承载上限
等待超时：100ms
编写连接池，实现一个接口 DateSource
开源数据源实现
DBCP
C3P0
Druid：阿里巴巴
使用这些数据库连接池后在项目就不需要写项目连接池了