JDBC操作MySQL数据库案例

JDBC操作MySQL数据库案例

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement; import org.junit.Test; public class JDBCTest {
@Test
public void test() throws Exception {
Connection con = null;//定义引用
Statement stmt = null;
ResultSet rs = null;
//规范的代码格式,try catch finally
try {
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/exam";
String username = "root";
String password = "123"; Class.forName(driverClassName); //加载
con = DriverManager.getConnection(url, username, password); //连接
stmt = con.createStatement(); //可以理解为创建SQL语句发射器 //executeUpdate方法,可以执行增删改语句(INSERT, UPDATE, DELETE),返回被改变的记录条数
String sql="DELETE FROM stu";
int r = stmt.executeUpdate(sql);
System.out.println("共删除了"+r+"条记录!"); //executeQuery方法,用于执行查询操作(SELECT),返回结果集
String sql2="select * from emp";
rs = stmt.executeQuery(sql2);
while(rs.next()) { //把光标向下移动一行,并判断下一行是否存在!
int empno = rs.getInt(1); //通过列编号来获取该列的值!
String ename = rs.getString("ename"); //通过列名称来获取该列的值
double sal = rs.getDouble("sal");
System.out.println(empno + ", " + ename + ", " + sal);
}
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
// 一定要关闭!!!!!!
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(con != null) con.close();
}
}
@Test
/**
* 预处理方式
* 优点:灵活,效率高,防SQL攻击
* SQL攻击例子:
* 若:sql = "select * from t_user where username='" + username + "' and password='" + password + "'";
* username = "a' or 'a'='a";
* password = "a' or 'a'='a";
* 最后拼成的语句为:
* select * from t_user where username='a' or 'a'='a" and password='a' or 'a'='a'
* 永远为true。
*/
public void test2() throws Exception {
Connection con = null;//定义引用
ResultSet rs = null;
PreparedStatement pstmt=null;
try {
String driverClassName = "com.mysql.jdbc.Driver";
//mysql默认预处理是关闭的,加上这两个参数之后可以开启预处理
String url = "jdbc:mysql://localhost:3306/exam?useServerPrepStmts=true&cachePrepStmts=true";
String username = "root";
String password = "123";
Class.forName(driverClassName);
con = DriverManager.getConnection(url, username, password); String sql="select * from emp where empno=? and job=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 1001);
pstmt.setString(2, "文员");
rs =pstmt.executeQuery();
if(rs.next())
{
System.out.println(rs.getString("ename"));
}
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(con != null) con.close();
}
}
}
上一篇:SharePoint solution and feature management with PowerShell


下一篇:CSS清除浮动_清除float浮动