CREATE DATABASE mydb;
#使用数据库
USE mydb;
###创建分类表
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT ,
cname VARCHAR(100)
);
#初始化数据
INSERT INTO category (cname) VALUES('家电');
INSERT INTO category (cname) VALUES('服饰');
INSERT INTO category (cname) VALUES('化妆品');
package bull01.JDBCBasis;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/mydb";
private static String user = "root";
private static String password = "root";
static {
try {
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnections() throws SQLException {
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
public static void closeResource(Connection conn,Statement st,ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
};
}
if(st != null) {
try {
st.close();
} catch (SQLException e) {
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
package bull02.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class CRUDDemo {
@Test
public void demo1() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
st = conn.createStatement();
int r = st.executeUpdate("insert into category (cname) values ('测试')");
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo2() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
st = conn.createStatement();
int r = st.executeUpdate("delete from category where cid = 7");
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo3() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
st = conn.createStatement();
int r = st.executeUpdate("update category set cname = '食品' where cid = 3");
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo4() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
st = conn.createStatement();
rs = st.executeQuery("select * from category");
while(rs.next()) {
int cid = rs.getInt("cid");
String cname = rs.getString("cname");
System.out.println(cid + "---" + cname);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
package bull02.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class PrepareDemo {
@Test
public void demo1() {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "insert into category (cid,cname) values (?,?)";
psmt = conn.prepareStatement(sql);
psmt.setInt(1, 4);
psmt.setString(2, "书本");
int r = psmt.executeUpdate();
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
@Test
public void demo2() {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "delete from category where cid = ?";
psmt = conn.prepareStatement(sql);
psmt.setInt(1, 8);
int r = psmt.executeUpdate();
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
@Test
public void demo3() {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "update category set cname = ? where cid = ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1, "汽车");
psmt.setInt(2, 4);
int r = psmt.executeUpdate();
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
@Test
public void demo4() {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "select * from category";
psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
while(rs.next()) {
Integer cid = rs.getInt("cid");
String cname = rs.getString("cname");
System.out.println(cid + "---" + cname);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
@Test
public void demo5() {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "select * from category where cid = ?";
psmt = conn.prepareStatement(sql);
psmt.setInt(1, 1);
rs = psmt.executeQuery();
if(rs.next()) {
Integer cid = rs.getInt("cid");
String cname = rs.getString("cname");
System.out.println(cid + "---" + cname);
}
else {
System.out.println("查询不到");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
}
采用预处理防SQL注入案例
USE mydb;
CREATE TABLE USER(
NAME VARCHAR(50) PRIMARY KEY,
passwd VARCHAR(50)
);
INSERT INTO USER (NAME,passwd) VALUES ('jack','123456');
package bull02.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
public class PreparedDemo {
@Test
public void method() {
String name = "jack #";
String passwd = "123456";
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnections();
String sql = "select * from user where name = ? and passwd = ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1, name);
psmt.setString(2, passwd);
rs = psmt.executeQuery();
if(rs.next()) {
System.out.println("登录成功!");
}
else {
System.out.println("登录失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResource(conn, psmt, rs);
}
}
}