Java Web程序设计——JDBC(二)
PreparedStatement对象
- PreparedStatement对象可以对SQL语句进行预编译,预编译的信息会存储在该对象中。当相同的SQL语句再次执行时,程序会使用PreparedStatement对象中的数据,而不需要对SQL语句再次编译去查询数据库,这样就大大的提高了数据的访问效率。
例:
package jdbc.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class Example02 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
try {
// 1.注册和加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
// 3.获取执行sql语句的对象
String sql = "insert into users values(?,?,?,?,?)";
pst = con.prepareStatement(sql);
// 4.执行sql前,要给占位符赋值
pst.setInt(1, 11);
pst.setString(2, "xw");
pst.setString(3, "159");
pst.setString(4, "xw@qq.com");
java.sql.Date date = new java.sql.Date(new Date().getTime());
pst.setDate(5, date); // 数据库里面的日期类型,java.sql.Date
int row = pst.executeUpdate();
if (row == 1) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
pst = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
}
注:java.sql.Date和java.util.Date关系:1.数据库插入日期值,一定要用java.sql.Date。
2.java.sql.Date是子类,java.util.Date是父类
3.两个Date之间相互转换:java.util.Date utilDate = new java.sql.Date();
java.sql.Date sqlDate = new java.sql.Date(new Date().java.util.Date().getTime();
ResultSet对象
- ResultSet主要用于存储结果集,可以通过next()方法由前向后逐个获取结果集中的数据,如果想获取结果集中任意位置的数据,则需要在创建Statement对象时,设置两个ResultSet定义的常量。
- 演示滚动结果集:
例:
package jdbc.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Example03 {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
// 3.获取执行sql语句的时候,支持滚动结果集
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// 4.执行sql语句
rs = stmt.executeQuery("select * from users");
// 5.遍历结果集
// 演示获取第二行的数据
rs.absolute(2);
System.out.println("获取第二行的值:" + rs.getString("name"));
//获取第一行的值
rs.beforeFirst(); //把光标定位第一行的前面
rs.next(); //把光标跳转到第一行
System.out.println("获取第一行的值:"+rs.getString("name"));
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
}
使用JDBC完成数据的增删改查
- 步骤:
- 创建javaBean封装数据:User
- 创建工具类:JDBCUtils,在开发中提取工具类,是为了简化代码,使用起来简单方便
- 创建一个Dao类:UsersDao
- 创建测试类:测试添加方法的类:JdbcInsertTest
测试查询所有方法的类:FindAllUserTest
测试查询一个对象的类:FindUserByIdTest
测试删除方法的类:UpdateUserTest
User
package jdbc.example;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
JDBCUtils
package jdbc.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
// 加载驱动,并建立数据库连接
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "";
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
// 关闭数据库连接,释放资源
public static void release(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, conn);
}
}
UsersDao
package jdbc.example;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
public class UsersDao {
// 添加用户操作
public boolean insert(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得statement对象
stmt = conn.createStatement();
// 发送sql语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql = "insect into users(id,name,password,email,birthday)" + "values(" + user.getId() + ",'"
+ user.getUsername() + "','" + user.getPassword() + "','" + user.getEmail() + "','" + birthday
+ "')";
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
// 查询所有的User对象
public ArrayList<User> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<User> list = new ArrayList<User>();
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得statement对象
stmt = conn.createStatement();
// 发送sql语句
String sql = "select * from users";
rs = stmt.executeQuery(sql);
// 处理结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
list.add(user);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 根据id查找指定的user
public User find(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得statement对象
stmt = conn.createStatement();
// 发送sql语句
String sql = "select * from users where id =" + id;
rs = stmt.executeQuery(sql);
// 处理结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
return user;
}
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 删除用户
public boolean delete(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得statement对象
stmt = conn.createStatement();
// 发送sql语句
String sql = "delete * from users where id =" + id;
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
// 修改用户
public boolean update(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得statement对象
stmt = conn.createStatement();
// 发送sql语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql = "update users ste name ='" + user.getUsername() + "' , password='" + user.getPassword()
+ "',email='" + user.getEmail() + "',birthday='" + birthday + "'where id=" + user.getId();
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
}
JdbcInsertTest
package jdbc.example;
import java.util.Date;
public class JdbcInsertTest {
public static void main(String[] args) {
// 向users表插入一个用户信息
UsersDao ud = new UsersDao();
User user = new User();
user.setId(4);
user.setUsername("blank");
user.setPassword("951");
user.setEmail("blank@qq.com");
user.setBirthday(new Date());
boolean b = ud.insert(user);
System.out.println(b);
}
}
FindAllUserTest
package jdbc.example;
import java.util.ArrayList;
public class FindAllUsersTest {
public static void main(String[] args) {
//创建一个名称为usersDao的对象
UsersDao usersDao = new UsersDao();
//将UsersDao对象的findAll()方法执行后的结果放入list集合
ArrayList<User> list = usersDao.findAll();
//循环输出集合中的数据
for(int i=0;i<list.size();i++) {
System.out.println("第"+(i+1)+"条数据的username值为:"+list.get(i).getUsername());
}
}
}
FindUserByIdTest
package jdbc.example;
import java.util.Date;
public class UpdateUserTest {
public static void main(String[] args) {
//修改User对象的数据
UsersDao usersDao = new UsersDao();
User user = new User();
user.setId(3);
user.setUsername("xm");
user.setPassword("753");
user.setEmail("xm@qq.com");
user.setBirthday(new Date());
boolean b = usersDao.update(user);
System.out.println(b);
}
}
UpdateUserTest
package jdbc.example;
import java.util.Date;
public class UpdateUserTest {
public static void main(String[] args) {
//修改User对象的数据
UsersDao usersDao = new UsersDao();
User user = new User();
user.setId(3);
user.setUsername("xm");
user.setPassword("753");
user.setEmail("xm@qq.com");
user.setBirthday(new Date());
boolean b = usersDao.update(user);
System.out.println(b);
}
}