PreparedStatement 与 Statement 区别?
1.使用statement执行sql,是以字符串拼接的方式,这时候存在sql注入的风险
名词解释:sql注入---一些非法分子,将一些特殊的字符串,通过字符串拼接的方式注入到系统原有的sql命令中,改变sql的运行逻辑,从而威胁到时据库数据的安全,这种现象就叫做sql注入。
2.使用PreparedStatement 可以避免sql注入问题
3.使用PreparedStatement可以提高程序的可读性、可维护性。
jdbc连接数据库 版本1 createStatement
利用 createStatement 针对于 增删改 dml
public class Test1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName ("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
String sql = "insert into user1 values(2,'bb')";
Statement statement = conn.createStatement ();
int i = statement.executeUpdate (sql);
System.out.println ("受影响的行数="+i);
if (statement!=null){ statement.close ();}
if (conn!=null){ conn.close ();}
}
}
jdbc 连接数据库 版本2 createStatement
利用 createStatement 针对于 查 dql
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName ("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "hr";
connection = DriverManager.getConnection (url, user, password);
String c_name = "ss";
String sql = "select * from user1 where c_name = '"+c_name+"'";
statement = connection.createStatement ();
resultSet = statement.executeQuery (sql);
while (resultSet.next ()){
int c_id = resultSet.getInt ("c_id");
String c_name1 = resultSet.getString ("c_name");
System.out.println (c_id+"的名称是:"+c_name1);
}
} catch (ClassNotFoundException e) {
e.printStackTrace ();
} catch (SQLException e) {
e.printStackTrace ();
} finally {
try {
if (resultSet != null){ resultSet.close ();}
if (statement != null){ statement.close (); }
if (connection !=null){ connection.close ();}
} catch (SQLException e) {
e.printStackTrace ();
}
}
}
}
jdbc连接数据库 版本3 prepareStatement针对于
利用 prepareStatement针对于 增删改 dml
public static void addbook(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName ("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "hr";
connection = DriverManager.getConnection (url, user, password);
String sql = "insert into book values (bookId.nextval,?,?,?,?)";
preparedStatement = connection.prepareStatement (sql);
preparedStatement.setString (1,"ddd");
preparedStatement.setDouble (2,66.6);
String str = "1999-12-25";
SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");
Date parse = sdf.parse (str);
long time = parse.getTime ();
java.sql.Date sqldate = new java.sql.Date (time);
preparedStatement.setDate (3,sqldate);
preparedStatement.setString (4,"这是一本好书");
int i = preparedStatement.executeUpdate ();
System.out.println ("受影响的行数="+i);
} catch (ClassNotFoundException e) {
e.printStackTrace ();
} catch (SQLException e) {
e.printStackTrace ();
} catch (ParseException e) {
e.printStackTrace ();
} finally {
try {
if (preparedStatement !=null){ preparedStatement.close (); }
if (connection!=null){ connection.close (); }
} catch (SQLException e) {
e.printStackTrace ();
}
}
}
jdbc连接数据库 版本4 prepareStatement
利用 prepareStatement针对于 查询 dql
public static List<Book> getBooksByPriceRange(double minPrice, double maxPrice) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet re = null;
try {
Class.forName ("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "hr";
conn = DriverManager.getConnection (url, user, password);
String sql = "select * from book where price between ? and ?";
pstmt = conn.prepareStatement (sql);
pstmt.setDouble (1, minPrice);
pstmt.setDouble (2, maxPrice);
re = pstmt.executeQuery ();
List<Book> books = new ArrayList<Book>();
while (re.next ()) {
int bookId = re.getInt ("bookId");
String bookName = re.getString ("bookName");
double price = re.getDouble ("price");
Date crTime = re.getDate ("crTime");
String describe = re.getString ("describe");
Book book = new Book ();
book.setBookId (bookId);
book.setBookName (bookName);
book.setPrice (price);
book.setCrTime (crTime);
book.setDescribe (describe);
books.add (book);
}
return books;
} catch (Exception e) {
e.printStackTrace ();
} finally {
try {
if (re != null) { re.close (); }
if (pstmt != null) { pstmt.close (); }
if (conn != null) { conn.close (); }
} catch (SQLException e) {
e.printStackTrace ();
}
}
return null;
}
jdbc连接数据库 版本5 prepareStatement
public static List<Book> getBooksByPriceRange1(String like) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet re = null;
try {
Class.forName ("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "hr";
conn = DriverManager.getConnection (url,user,password);
String sql = "select * from book where bookName like ?";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1, "%"+like+"%");
re = pstmt.executeQuery ();
List<Book> books = new ArrayList<Book>();
while (re.next ()) {
int bookId = re.getInt (1);
String bookName = re.getString (2);
double price = re.getDouble (3);
Date crTime = re.getDate (4);
String describe = re.getString (5);
Book book = new Book ();
book.setBookId (bookId);
book.setBookName (bookName);
book.setPrice (price);
book.setCrTime (crTime);
book.setDescribe (describe);
books.add (book);
}
return books;
} catch (Exception e) {
e.printStackTrace ();
} finally {
try {
if (re != null) { re.close (); }
if (pstmt != null) { pstmt.close (); }
if (conn != null) { conn.close (); }
} catch (SQLException e) {
e.printStackTrace ();
}
}
return null;
}
jdbc.properties 小配置文件
driver = oracle.jdbc.OracleDriver
url =jdbc:oracle:thin:@localhost:1521:xe
user = hr
password =hr
jdbc 工具类使用 封装的工具类
package busywork.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
public class JDBCUtil {
public static ThreadLocal<Connection> tr = new ThreadLocal<Connection>();
private static Properties p = new Properties();
static{
try(InputStream is =JDBCUtil.class.getResourceAsStream("/jdbc.properties")){
p.load(is);
Class.forName(p.getProperty("driver"));
}catch(Exception e){
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(p.getProperty("url"),p.getProperty("user"),p.getProperty("password"));
} catch (Exception e) {e.printStackTrace();}
return conn;
}
public static void close(Connection conn,PreparedStatement pstmt){
if(pstmt!=null){
try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}
}
if(conn!=null){
try {conn.close();} catch (SQLException e) {e.printStackTrace();}
}
}
public static void close(Connection conn,PreparedStatement patmt,ResultSet re){
if(re!=null){
try {re.close();} catch (SQLException e) {e.printStackTrace();}
}
if(patmt!=null){
try {
patmt.close();
tr.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
tr.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static java.util.Date UtilDate(String str){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
java.util.Date utilDate = sdf.parse(str);
return utilDate;
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
public static java.sql.Date toSqlDate(java.util.Date utilDate){
long time = utilDate.getTime();
java.sql.Date sqlDate = new java.sql.Date(time);
return sqlDate;
}
}
jdbc连接数据库 版本6 利用 JDBCUtil工具类开发
在 prepareStatement 的基础上 利用 JDBCUtil工具类 针对于 增删改 dml 进行开发
public void addbook(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "insert into book values (bookId.nextval,?,?,?,?)";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1,"是是是");
pstmt.setDouble (2,99.9);
pstmt.setDate (3,JDBCUtil.toSqlDate (new java.util.Date ()));
pstmt.setString (4,"好好好");
int i = pstmt.executeUpdate ();
System.out.println ("受影响的行数="+i);
} catch (Exception e) {
e.printStackTrace ();
} finally {
JDBCUtil.close(conn, pstmt);
}
}
jdbc连接数据库 版本7 利用 JDBCUtil工具类开发
在 prepareStatement 的基础上 利用 JDBCUtil工具类 针对于 增查 dql 进行开发
public List<Book> queryAllBooks(String likebook){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<Book> books = new ArrayList<Book> ();
Book book = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "select * from book where bookName like ?";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1,"%"+likebook+"%");
rs = pstmt.executeQuery ();
while (rs.next ()){
book = new Book (rs.getInt (1),rs.getString (2),rs.getDouble (3),rs.getDate (4),rs.getString (5));
books.add (book);
}
return books;
} catch (Exception e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt,rs);
}
return null;
}
jdbc连接数据库 版本8 对dao层进行封装用junit测试类 进行测试
ORM 对象关系映射,DAO 数据访问对象
对dao层进行封装,用junit测试类 进行测试
1.封装实体类
import java.util.Date;
public class Book {
private Integer bookId;
private String bookName;
private double price;
private Date crTime;
private String describe;
}
2.定义dao实体类对应的接口
public interface BookDao {
public void addBook(Book book);
public void deleteBookById(Integer bookId);
public void updateBookById(Book book);
public Book selectBookById(Integer bookId);
public List<Book> selectBookLike(String likebookName);
}
3.实现dao实体类对应的接口中的方法
public class BookDaoImpl implements BookDao{
@Override
public void addBook(Book book) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "insert into book values (bookId.nextval,?,?,?,?)";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1,book.getBookName ());
pstmt.setDouble (2,book.getPrice ());
pstmt.setDate (3, new java.sql.Date(book.getCrTime ().getTime ()));
pstmt.setString (4,book.getDescribe ());
int i = pstmt.executeUpdate ();
System.out.println ("受影响的条数是:"+i);
} catch (Exception e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt);
}
}
@Override
public void deleteBookById(Integer bookId) {
Connection conn=null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "delete from book where bookId = ?";
pstmt = conn.prepareStatement (sql);
pstmt.setInt (1,bookId);
int i = pstmt.executeUpdate ();
System.out.println ("受影响的行数是="+i);
} catch (SQLException e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt);
}
}
@Override
public void updateBookById(Book book) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "update book set bookName=?,price=?,crTime = ?,describe =? where bookId=?";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1,book.getBookName ());
pstmt.setDouble (2,book.getPrice ());
pstmt.setDate (3,JDBCUtil.toSqlDate (book.getCrTime ()));
pstmt.setString (4,book.getDescribe ());
pstmt.setInt (5,book.getBookId());
int i = pstmt.executeUpdate ();
System.out.println ("受影响的行数是:"+i);
} catch (SQLException e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt);
}
}
@Override
public Book selectBookById(Integer bookId) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet re = null;
Book book1 = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "select * from book where bookId = ?";
pstmt = conn.prepareStatement (sql);
pstmt.setInt (1,bookId);
re = pstmt.executeQuery ();
if (re.next ()){
book1 = new Book ();
book1.setBookId (re.getInt ("bookId"));
book1.setBookName (re.getString ("bookName"));
book1.setPrice (re.getDouble ("price"));
book1.setCrTime (re.getDate ("crTime"));
book1.setDescribe (re.getString ("describe"));
}
return book1;
} catch (SQLException e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt,re);
}
return null;
}
@Override
public List<Book> selectBookLike(String likebookName) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet re = null;
ArrayList<Book> books = null;
Book book = null;
try {
conn = JDBCUtil.getConnection ();
String sql = "select * from book where bookName like ?";
pstmt = conn.prepareStatement (sql);
pstmt.setString (1,"%"+likebookName+"%");
re = pstmt.executeQuery ();
books = new ArrayList<> ();
while (re.next ()){
book = new Book ();
book.setBookId (re.getInt ("bookId"));
book.setBookName (re.getString ("bookName"));
book.setPrice (re.getDouble ("price"));
book.setCrTime (re.getDate ("crTime"));
book.setDescribe (re.getString ("describe"));
books.add (book);
}
return books;
} catch (SQLException e) {
e.printStackTrace ();
} finally {
JDBCUtil.close (conn,pstmt,re);
}
return null;
}
}
4.junit Test 测试类分别测试
import busywork.entity.Book;
import busywork.util.JDBCUtil;
import org.junit.Test;
import java.util.List;
public class BookTest {
BookDao dao = new BookDaoImpl ();
@Test
public void test1(){
BookDao dao = new BookDaoImpl ();
Book book = new Book ();
book.setBookName ("将军在上");
book.setPrice (66.6);
String date = "1999-12-23";
book.setCrTime (JDBCUtil.UtilDate (date));
book.setDescribe ("花木兰转世");
dao.addBook (book);
}
@Test
public void test2(){
dao.deleteBookById (8);
}
@Test
public void test3(){
Book book = new Book ();
book.setBookId (4);
book.setBookName ("几度夕阳红");
book.setPrice (88.8);
book.setCrTime (JDBCUtil.UtilDate ("2018-12-25"));
book.setDescribe ("像一首老歌");
dao.updateBookById (book);
}
@Test
public void test4(){
Book book = dao.selectBookById (13);
System.out.println (book);
}
@Test
public void test5(){
List<Book> books = dao.selectBookLike ("嬛");
for (Book book : books) {
System.out.println (book);
}
}
}
用jdbc获取表格数据的总条数 3种方式
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import com.mysql.jdbc.Statement;
public class MySQLDao {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
Statement sta = (Statement) conn.createStatement(ResultSet.CONCUR_READ_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE);
String sql = "select * from homework";
ResultSet rs =sta.executeQuery(sql);
rs.last();
System.out.println("行数:"+rs.getRow());
rs.first();
int count=1;
while(rs.next()){
count++;
}
System.out.println("行数:"+count);
sql = "select count(*) from homework";
rs = sta.executeQuery(sql);
rs.next();
int row = rs.getInt(1);
System.out.println("行数:"+row);
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}