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);
}
}
386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
