加强版
package it.cast.demo; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; /** * 学习 PreparedStatement * 防sql攻击 * @author 10958 * */ public class Demo3 { /** * 登录 * 使用username和password去查询数据库 * 若查出结果集 则正确true * 这个存在sql攻击 所以用2 * @param username * @param password * @return * @throws ClassNotFoundException * @throws SQLException */ public boolean login(String username,String password) throws ClassNotFoundException, SQLException { //1得到Connection //2得到Statement //3得到ResultSet //4rs.next()返回的是什么 我们就返回啥 String driverClassName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/mydb3"; String mysqlUsername = "root"; String mysqlPassword = "123"; Class.forName(driverClassName); Connection con = DriverManager.getConnection(url,mysqlUsername,mysqlPassword); Statement stmt = con.createStatement(); String sql = "select * from t_user where username='"+username+"' and password = '"+ password+"'"; System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); return rs.next(); } public boolean login2(String username,String password) throws ClassNotFoundException, SQLException { //1得到Connection //2得到Statement //3得到ResultSet //4rs.next()返回的是什么 我们就返回啥 String driverClassName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/mydb3"; String mysqlUsername = "root"; String mysqlPassword = "123"; Class.forName(driverClassName); Connection con = DriverManager.getConnection(url,mysqlUsername,mysqlPassword); // Statement stmt = con.createStatement(); // String sql = "select * from t_user where username='"+username+"' and password = '"+ password+"'"; // System.out.println(sql); // ResultSet rs = stmt.executeQuery(sql); // return rs.next(); / /* * 得到PreparedStatement * 1.给出sql模版 :所有的参数用?来替代 * 2调用Connection方法 得到PreparedStatement * 3.调用PreparedStatement的getXxx()方法给?赋值 * 4.调用pstmt的executeUpdate或 Query */ String sql = "select * from t_user where username=? and password=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, username); //给第一个问号赋值 值为username; pstmt.setString(2, password); //第二个问号赋值 ResultSet rs = pstmt.executeQuery(); return rs.next(); } @Test public void fun1() throws ClassNotFoundException, SQLException{ /* * sql 攻击 * */ String username = "a' or 'a'='a"; String password = "a' or 'a'='a"; boolean bool = login(username, password); System.out.println(bool); //login("zhangSan", "123"); } @Test public void fun2() throws ClassNotFoundException, SQLException{ String username = "zhangSan"; String password = "123"; boolean bool = login2(username, password); System.out.println(bool); } @Test public void fun3() throws SQLException{ Connection con = JdbcUtils.getConnection(); System.out.println(con); } }创建一个1.0版本的jdbc工具类
package it.cast.demo; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class JdbcUtils { private static Properties props = null; // 只在JdbcUtils类被加载时执行一次 static { // 给pros初始化 即加载dbconfig.properties文件到pros try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties"); props = new Properties(); props.load(in); } catch (IOException e) { throw new RuntimeException(e); } // 加载驱动类 try { Class.forName(props.getProperty("driverClassName")); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } //获取连接 public static Connection getConnection() throws SQLException { /* * 1.加载配置文件 2.加载驱动类 3/调用DriverManager.getConnection() * */ // InputStream in = // JdbcUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties"); // Properties props = new Properties(); // props.load(in); return DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password")); } }往mysql存取文件
package it.cast.demo; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.rowset.serial.SerialBlob; import org.apache.commons.io.IOUtils; import org.junit.Test; import sun.nio.ch.IOUtil; /** * 大数据 * @author 10958 * */ public class Demo4 { /** * 把mp3保存到数据库 * @throws SQLException * @throws IOException * @throws FileNotFoundException */ @Test public void fun1() throws SQLException, FileNotFoundException, IOException{ Connection con = JdbcUtils.getConnection(); String sql = "INSERT INTO tab_bin values(?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setInt(1, 2); pstmt.setString(2, "星空.MP3"); /* * 需要得到Blob * 把文件转化成byte[] * 在创建BLob */ byte[] bytes = IOUtils.toByteArray(new FileInputStream("D:/星空.mp3")); Blob blob = new SerialBlob(bytes); pstmt.setBlob(3, blob); pstmt.executeUpdate(); } //读取MP3 @Test public void fun2() throws SQLException, IOException{ Connection con = JdbcUtils.getConnection(); String sql = "select * from tab_bin"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); //获取名为data的列数据 if (rs.next()) { Blob blob = rs.getBlob("data"); /* * 把Blob变成硬盘上的文件 * 1.通过Blob得到输入流对象 * 2.自己创建输出流对象 * 3.把输入流的数据写入到输出流中 */ InputStream in = blob.getBinaryStream(); OutputStream out = new FileOutputStream("D:/xingkong.mp3"); IOUtils.copy(in, out); } } }批处理 首先需要在url后面加上rewriteBatchedStatements=true
例如:String url = "jdbc:mysql://localhost:3306/exam?reewriteBatchedStatements=true"
