实现JDBC的三种法(记录)
第一种方法(开发推荐):
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class JDBC_preparestatement {
private static final String url="jdbc:mysql://localhost:3306/javadb";
private static final String username = "root";
private static final String password = "";
public static void update() throws ClassNotFoundException, SQLException {
//a,导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");
//b,与数据库建立连接
Connection connection = DriverManager.getConnection(url,username,password);
//c,发送sql语句
String sql = "insert into student values(?,?,?,?);";//开发推荐使用
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, 81);
stmt.setString(2, "小黑");
stmt.setInt(3, 22);
stmt.setInt(4, 100);
int count = stmt.executeUpdate();
//d,处理结果集
if(count>0){
System.out.println("操作成功");
}else{
System.out.println("操作失败");
}
//e,关闭对象
stmt.close();
connection.close();
}
public static void select() throws ClassNotFoundException, SQLException{
//导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");
//与数据库建立连接
Connection connection = DriverManager.getConnection(url,username,password);
//写查询的sql语句
String sql ="SELECT * from student;";
//创建statement事务执行sql语句
PreparedStatement stmt = connection.prepareStatement(sql);
//使用事务对象的executeQuery获取结果集
ResultSet rs = stmt.executeQuery(sql);
// 处理结果集,但指针的下一行有数据时,继续循环,否则停止。
while(rs.next()){
//使用rs.getXxx("字段名")来获取对象
int sno = rs.getInt("id");
String name = rs.getString("name");
//对结果进行打印
System.out.println(sno+"---"+name);
}
//最后一个开的第一个关
rs.close();
stmt.close();
//第一个开的最后一个关
connection.close();
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//select();
update();
}
}
第二种方法(比较规范):
package aaa;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Later {
private static final String url="jdbc:mysql://localhost:3306/javadb";
private static final String username = "root";
private static final String password = "";
public static void update() throws ClassNotFoundException, SQLException {
//a,导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");
//b,与数据库建立连接
Connection connection = DriverManager.getConnection(url,username,password);
//c,发送sql语句
Statement stmt = connection.createStatement();
String sql = "show DATABASES;";
int count = stmt.executeUpdate(sql);//返回值表示增删改几条数据
//d,处理结果集
if(count>0){
System.out.println("操作成功");
}else{
System.out.println("操作失败");
}
//e,关闭对象
stmt.close();
connection.close();
}
public static void select() throws ClassNotFoundException, SQLException{
//导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");
//与数据库建立连接
Connection connection = DriverManager.getConnection(url,username,password);
//创建statement事务执行sql语句
Statement stmt = connection.createStatement();
//写查询的sql语句
String sql ="SELECT * from student;";
//使用事务对象的executeQuery获取结果集
ResultSet rs = stmt.executeQuery(sql);
// 处理结果集,但指针的下一行有数据时,继续循环,否则停止。
while(rs.next()){
//使用rs.getXxx("字段名")来获取对象
int sno = rs.getInt("id");
String name = rs.getString("name");
//对结果进行打印
System.out.println(sno+"---"+name);
}
//最后一个开的第一个关
rs.close();
stmt.close();
//第一个开的最后一个关
connection.close();
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
select();
//update();
}
}
第三种方法(用于测试):
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBHelper {
private static final String driver = "com.mysql.jdbc.Driver";//数据库连接
private static final String url = "jdbc:mysql://localhost:3306/javadb?useUnicode=true&characterEncoding=UTF-8";
private static final String username = "root";
private static final String password = "";
private static Connection conn=null;
//静态代码块负责加载驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
if(conn==null){
conn = DriverManager.getConnection(url,username,password);
return conn;
}
return conn;
}
public static void main(String[] args) {
try {
Connection conn = DBHelper.getConnection();
if(conn!=null){
System.out.println("数据库连接正常");
}else {
System.out.println("数据库连接失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
sql注入演示:
源sql语句:
select count(*) from student where id='"+username+"' and age='"+passwordString+"'
输入(–后面有空格):
任意值 ‘ or 1=1 --
sql注入后:select count(*) from student where id='asdfads ' or 1=1 -- ' and age='"+passwordString+"'
sql注入:将客户端输入的内容和开发人员的sql语句混为一体.