jdbc简介
jdbc:java database connectivity
作用:
1、连接数据库
2、向数据库发送sql
3、可以对数据库中的数据进行处理
java提供的接口:
java.sql.Driver 驱动的管理
java.sql.Connection 连接的管理
java.sql.Statement 操作数据库管理
java.sql.ResultSet 结果集
快速入门:
1、jar包:
java project 需要新建lib文件,将jar放入其中,需要build path
web project jar 包 放入 webroot/web-inf/lib 即可,不需要build path
2、代码(步骤可以想像u盘的驱动)
第一步:加载驱动
第二步:创建连接
第三步:创建操作数据库的对象
第四步:操作数据并返回结果
第五步:释放资源
package com.itcast.base;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.jdbc.Driver;
public class Rumen {
public static void main(String[] args) throws Exception {
DriverManager.registerDriver(new Driver());//第一步:加载驱动
String url = "jdbc:mysql://localhost:3306/day06?useUnicode=true&characterEncoding=utf-8";
Connection conn = DriverManager.getConnection(url, "root", "root");//第二步:创建连接
Statement st = conn.createStatement();//第三步:创建操作数据库的对象
String sql = "select * from user";
ResultSet rs = st.executeQuery(sql);//第四步:操作并返回结果
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString(2);
String password = rs.getString(3);
String email = rs.getString(4);
System.out.println(id+"---"+name+"---"+password+"---"+email);
}
rs.close(); //第五步:释放资源
st.close();
conn.close();
}
}
详细学习:
static(注意复习下静态,又忘了) 只加载定义一次
静态(static)代码块作用:
(一)当类加载到内存中会执行静态代码块中的代码
(二)只执行一次
(一)注册驱动(使用反射):
Class.forName("com.mysql.jdbc.Driver");
(1)解决代码冗余
(2)只注册一次驱动
(二)创建连接:
Connection conn = DriverManager.getConnection(url,user,password);
url: jdbc:mysql://localhost:3306/day06?useUnicode=true&characterEncoding=utf-8
jdbc:mysql://[ip地址]:[端口号]/[库名]
本地: jdbc:mysql:///库名
(三)创建操作数据库的对象:
Statement sta = con.createStatement();
(四)操作数据库,返回结果
executeQuery(sql) //返回结果集
executeUpdate(sql) //返回受影响的条数
execute(sql) //写select返回true,写update,delete,insert返回false;
(五)结果集(ResultSet)
rs.getXX(String 字段的名字);
rs.getXX(int 索引值(从1开始));
Junit测试(使用情况):
没有参数
没有返回值
没有static修饰
工具包实现crud
package com.itcast.jdbc;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
public class Utile {
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql:///day06", "root", "root");
return conn;
}
public static void closeResource(ResultSet rs,Statement st,Connection con){
try {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(con!=null){
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.itcast.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class UtileTest {
public static void main(String[] args) throws Exception {
//add();
//delete();
//update();
select();
}
private static void select() throws Exception {
Connection con = Utile.getConnection();
Statement st = con.createStatement();
ResultSet rs=st.executeQuery("select * from user");
while(rs.next()){
int id = rs.getInt(1);
//String name = rs.getString(2);
String name = rs.getString("username");
String password = rs.getString(3);
String email = rs.getString(4);
System.out.println(id+"----"+name+"----"+password+"-----"+email);
}
Utile.closeResource(rs, st, con);
}
private static void update() throws Exception {
Connection con = Utile.getConnection();
Statement st = con.createStatement();
int i = st.executeUpdate("update user set username='lisi' where id=3");
if(i!=0){
System.out.println("update success!");
}else{
System.out.println("update fail");
}
}
private static void delete() throws Exception {
Connection con = Utile.getConnection();
Statement st = con.createStatement();
int i = st.executeUpdate("delete from user where id=4");
if(i!=0){
System.out.println("delete success");
}else{
System.out.println("delete fail");
}
st.close();
con.close();
}
private static void add() throws Exception {
Connection con = Utile.getConnection();
Statement st = con.createStatement();
int i = st.executeUpdate("insert into user values(null,'liming','1234','liming@qq.com')");
if(i!= 0){
System.out.println("insert success!");
}else{
System.out.println("insert fail");
}
st.close();
con.close();
}
}
SQL注入:
private static void select() throws Exception {
Connection con = Utile.getConnection();
Statement st = con.createStatement();
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String name = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
String sql = "select * from user where username ='"+name+"'and password ='"+password+"'";
System.out.println(sql);
ResultSet rs=st.executeQuery(sql);
while(rs.next()){
int id = rs.getInt(1);
//String name = rs.getString(2);
String username = rs.getString("username");
String userpassword = rs.getString(3);
String email = rs.getString(4);
System.out.println(id+"----"+username+"----"+userpassword+"-----"+email);
}
Utile.closeResource(rs, st, con);
}
数据全部可以查询出来了
解决sql注入:
PreparedStatement:表示预编译的sql语句(预编译完后,所有输入的字符都不能够是关键字)
?:占位符。(占一个位置)
PrepareStatement的CRUD:
jdbc.properties区分大小写
package com.itcast.jdbc;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ResourceBundle;
public class Utile {
private static final String driverName;//必须加final,否则能够通过反射得到
private static final String url;
private static final String user;
private static final String password;
static{
ResourceBundle bun = ResourceBundle.getBundle("jdbc");
driverName = bun.getString("driverName");
url = bun.getString("URL");
user = bun.getString("user");
password = bun.getString("password");
}
static{
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
Connection conn = (Connection) DriverManager.getConnection(url, user, password);
return conn;
}
public static void closeResource(ResultSet rs,Statement st,Connection con){
try {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(con!=null){
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.itcast.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SqlTest {
public static void main(String[] args) throws Exception {
//add();
//delete();
update();
//select();
}
private static void select() throws Exception {
Connection con = Utile.getConnection();
String sql = "select * from user where username = ? and password = ?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "tom");
st.setString(2, "123");
ResultSet rs=st.executeQuery();
while(rs.next()){
int id = rs.getInt(1);
//String name = rs.getString(2);
String username = rs.getString("username");
String userpassword = rs.getString(3);
String email = rs.getString(4);
System.out.println(id+"----"+username+"----"+userpassword+"-----"+email);
}
Utile.closeResource(rs, st, con);
}
private static void update() throws Exception {
Connection con = Utile.getConnection();
String sql = "update user set username=? where id=?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "lisisiw");
st.setString(2, "3");//这块是int写成String也可以
int i = st.executeUpdate();
if(i!=0){
System.out.println("update success!");
}else{
System.out.println("update fail");
}
}
private static void delete() throws Exception {
Connection con = Utile.getConnection();
String sql = "delete from user where id=?";
PreparedStatement st = con.prepareStatement(sql);
st.setInt(1, 2);
int i = st.executeUpdate();
if(i!=0){
System.out.println("delete success");
}else{
System.out.println("delete fail");
}
Utile.closeResource(null, st, con);
}
private static void add() throws Exception {
Connection con = Utile.getConnection();
String sql = "insert into user values(null,?,?,?)";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "lili");
st.setString(2, "wwwl");
st.setString(3, "lili@qq");
int i = st.executeUpdate();
if(i!= 0){
System.out.println("insert success!");
}else{
System.out.println("insert fail");
}
Utile.closeResource(null, st, con);
}
}