一.
import java.sql.*;
public class TestDemo {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3307/choose?useSSL=true";
// 数据库的用户名与密码,需要根据自己的设置
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
Statement stmt = null;
try {
// 注册 JDBC 驱动
Class.forName("com.mysql.jdbc.Driver");
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println(" 实例化Statement对象...");
stmt = conn.createStatement();
String sql;
sql = "select * from student";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("student_id");
String name = rs.getString("student_name");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 站点名称: " + name);
System.out.print("\n");
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
} // 什么都不做
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
二.
import java.sql.*;
// ResultSet 结果集
//boolean next()
//将光标从当前位置向前移一行。 如果返回真 指向下一条数据 如果返回假,没有下一条数据了
//int getInt(int columnIndex)
//以 Java 编程语言中 int 的形式获取此 ResultSet 对象的当前行中指定列的值。
//int getInt(String columnLabel)
//以 Java 编程语言中 int 的形式获取此 ResultSet 对象的当前行中指定列的值
//getXXX()
public class CRUDDemo {
// 连接对象
static Connection con;
// 查询对象
static Statement stmt;
// 结果集对象
static ResultSet rs;
// static 静态的
// final 不可改变
// 服务器的地址
static final String URL = "jdbc:mysql://localhost:3307/choose?useSSL=false";
// 用户名
static final String USER = "root";
// 密码
static final String PASSWORD = "123456";
static {
// 1. 加载驱动
// com.mysql.jdbc.Driver类
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 2. 连接数据库
static void connect() {
try {
con = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 3. 释放资源
static void destoryResource() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
con = null;
}
}
public static void main(String[] args) {
// 1. 增加数据
// add();
// // 2. 删除数据
// del();
//
// // 3. 修改数据
// modify();
//
// // 4. 查询数据
// query();
query2();
}
private static void query2() {
// 1. 连接数据库
connect();
// 2. 查询
try {
stmt = con.createStatement();
String sql = "select count(*) from student;";
rs = stmt.executeQuery(sql);// executeQuery返回的是结果集
if (rs.next())
{
int count = rs.getInt(1);
System.out.println( "学生人数" + count);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 3. 关闭连接释放资源
destoryResource();
}
}
private static void query() {
// 1. 连接数据库
connect();
// 2. 查询
try {
stmt = con.createStatement();
String sql = "select * from student;";
rs = stmt.executeQuery(sql);// executeQuery返回的是结果集
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println(id + "," + name);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 3. 关闭连接释放资源
destoryResource();
}
}
private static void modify() {
// TODO Auto-generated method stub
// 1. 连接数据库
connect();
// 2. 执行查询
try {
String sql = "update student set student_contact='110' where student_name='机器人10';";
stmt = con.createStatement();
int result = stmt.executeUpdate(sql);
if (result > 0)
System.out.println("更新数据成功");
else
System.out.println("更新数据失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 3. 关闭连接释放资源
destoryResource();
}
}
private static void del() {
// 1. 连接数据库
connect();
// 2. 执行查询
try {
String sql = "delete from student where student_id=2020001;";
stmt = con.createStatement();
int result = stmt.executeUpdate(sql);
if (result > 0)
System.out.println("删除数据成功");
else
System.out.println("删除数据失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 3. 关闭连接释放资源
destoryResource();
}
}
private static void add() {
// 1. 连接数据库
connect();
// 2. 执行查询
try {
String sql = "insert into student values(2020001,'李丽','123123123',null,null);";
stmt = con.createStatement();
int result = stmt.executeUpdate(sql);
if (result > 0)
System.out.println("增加数据成功");
else
System.out.println("增加数据失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 3. 关闭连接释放资源
destoryResource();
}
}
}
三.
public class _1PreparedStatementDemo {
public static void main(String[] args)
{
// addStudent(2021001,"郭德纲");
// login("qiang","123456");
// login("qiang","123");
// SQL注入攻击
//login("qiang","aaa' or '1' = '1");
// 采用PreparedStatement 可以防止注入攻击
login2("qiang","aaa' or '1' = '1");
}
private static void login2(String name, String password)
{
// 1. 创建连接
Connection con = DBUtils.getConnection();
// 2.
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select count(*) from admin where name=? and password=?;";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,password);
rs = pstmt.executeQuery();
if(rs.next())
{
int count = rs.getInt(1);
if(count>0) System.out.println("登陆成功");
else System.out.println("登陆失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtils.destoryResource(rs, pstmt, con);
}
}
private static void login(String name, String password)
{
// 1. 创建连接
Connection con = DBUtils.getConnection();
// 2.
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
String sql = "select count(*) from admin where name='"+name+"' and password='"+password+"';";
System.out.println(sql);
rs = stmt.executeQuery(sql);
if(rs.next())
{
int count = rs.getInt(1);
if(count>0) System.out.println("登陆成功");
else System.out.println("登陆失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtils.destoryResource(rs, stmt, con);
}
}
private static void addStudent(int stu_id, String stu_name)
{
// 1. 连接数据库
Connection con = DBUtils.getConnection();
PreparedStatement pstmt = null;
// 2. 查询
try {
String sql = "insert into student values(?,?,'123123123',null,null);";
pstmt = con.prepareStatement(sql);
// 设定参数
pstmt.setInt(1, stu_id);
pstmt.setString(2, stu_name);
// 执行
int result = pstmt.executeUpdate(sql);
if (result > 0)
System.out.println("增加数据成功");
else
System.out.println("增加数据失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.destoryResource(null, pstmt, con);
}
}
}