JavaWeb学习心得之JDBC对数据库CRUD

一、statement对象简介

       Jdbc中statement对象用于向数据库发送SQL语句，statement对象executeUpdate方法，用于向数据库发送增、删、改的sql语句，执行完后返回整数；executeQuery方法用于向数据库发送查询语句，返回代表查询结果的ResultSet对象。

CRUD操作

1.create

Statement st = conn.createStatement();String sql = "insert into users(id,name,password,email,birthday) values (4,'天山童老','123456','[e-mail]','2012-12-12')";int result = st.executeUpdate(sql);if(result>0){System.out.println("插入成功!!!!");}

2.update

Statement st = conn.createStatement();String sql = "update users set name='caocao1' where name='caocao'";int result = st.executeUpdate(sql);if(result>0){System.out.println("跟新成功!!!!");}

3.delete

Statement st = conn.createStatement();String sql = "delete from users where id = 4";int result = st.executeUpdate(sql);if(result>0){System.out.println("删除成功!!!!");}

4.read

Statement st = conn.createStatement();String sql = "select * from users";ResultSet result = st.executeQuery(sql);while(result.next()){//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中}

二、使用JDBC对数据库操作

1.环境搭建：创建表users和插入数据

CREATE DATABASE jdbcdemo CHARACTER SET utf8 COLLATE utf8_general_ciCREATE TABLE users(id int PRIMARY KEY,name VARCHAR(40),password VARCHAR(40),email VARCHAR(60),birthday date );INSERT INTO users(id,name,password,email,birthday) VALUES(1,'zhangfei','123456','[e-mail]','1987-2-18');INSERT INTO users(id,name,password,email,birthday) VALUES(2,'caocao','123456','[e-mail]','1989-8-16');INSERT INTO users(id,name,password,email,birthday) VALUES(3,'libai','123456','[e-mail]','1995-3-22');

2.创建JavaWeb工程，并导入MySql数据库驱动

3.创建db.properties文件

4.编写JdbcUtils工具类

import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtils {private static String url = null;private static String driver = null;private static String username = null;private static String password = null;static{InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();try {properties.load(in);url = properties.getProperty("url");driver = properties.getProperty("driver");username = properties.getProperty("username");password = properties.getProperty("password");Class.forName(driver);} catch (Exception e) {e.printStackTrace();}finally {try {in.close();} catch (IOException e) {e.printStackTrace();}}}/* * 获取数据库连接对象 */public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url,username,password);}/* *  要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象 */public static void release(Connection conn,Statement st,ResultSet rs){if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if(st!=null){try {st.close();} catch (SQLException e) {e.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

5.CURD操作

import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;public class JdbcDemo02 {public void insert(){Connection conn = null;Statement st = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql = "insert into users(id,name,username,password,email,birthday) values(5,'唐明皇','123456','[e-mail]','2001-12-12')";int result = st.executeUpdate(sql);if(result>0){System.out.println("插入成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void delete(){Connection conn = null;Statement st = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql = "delete ftom users where id = 5";int result = st.executeUpdate(sql);if(result>0){System.out.println("删除成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void update(){Connection conn = null;Statement st = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql = "update users set name = '杨贵妃' where id = 5";int result = st.executeUpdate(sql);if(result>0){System.out.println("跟新成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void find(){Connection conn = null;Statement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql = "select * from users where id = 5";    rs = st.executeQuery(sql);if(rs.next()){System.out.println(rs.getString("name"));}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, rs);}}}

三、PreparedStatement对象简介

       　　PreperedStatement是Statement的子类，它的实例对象可以通过调用Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题。
　　Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

CURD操作

package com.hanxin;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.Date;public class JdbcDemo3 {public void insert(){Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "insert into users (id,name,password,email,birthday) values(?,?,?,?,?)";st = conn.prepareStatement(sql);st.setInt(1, 6);st.setString(2, "鲁迅");st.setString(3, "123456");st.setString(4, "[e-mail]");st.setDate(5, new java.sql.Date(new Date().getTime()));int result = st.executeUpdate();if(result>0){System.out.println("插入成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void update(){Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "update users set name=? where id = ?";st = conn.prepareStatement(sql);st.setString(1,"李大钊");st.setInt(2, 6);int result = st.executeUpdate();if(result>0){System.out.println("跟新成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void delete(){Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "delete from users where id = ?";st = conn.prepareStatement(sql);st.setInt(1, 6);int result = st.executeUpdate();if(result>0){System.out.println("删除成功");}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}public void find(){Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql = "select * from users";st = conn.prepareStatement(sql);rs = st.executeQuery();while(rs.next()){System.out.println(rs.getString("name"));}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, rs);}}}