使用JDBC对数据库的一些简单操作
1.首先我把获取驱动和创建连接还有关闭连接给整合出来了,这样后面直接调用就好了,就不用每次用到都要写一遍,省了很多麻烦。
import java.sql.*;
public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/java7?useSSL=true&characterEncoding=utf-8";
private static String user = "root";
private static String password = "123";
//1.获取驱动
static{
try{
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
//2.创建连接
public static Connection get_Conn()throws SQLException{
Connection conn = DriverManager.getConnection(url, user, password);
// System.out.println("数据库连接成功" + conn);
return conn;
}
//3.关闭连接
public static void get_ClossConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws SQLException {
if(rs !=null){
rs.close();
}
if(pstm !=null){
pstm.close();
}
if(conn !=null){
conn.close();
}
}
//单元测试(白盒测试)
public static void main(String[] args){
try{
get_Conn();
}catch (SQLException e){
e.printStackTrace();
}
}
}
2.使用JDBC对数据库中某个表进行简单的遍历操作:
import java.sql.*;
public class UserList {
//输出全部
public static void userList(){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("select * from tb_user");
rs = pstm.executeQuery();
while(rs.next()){
System.out.println("[用户ID=" + rs.getInt(1) + ",用户名=" + rs.getString(2) + ",用户密码=" + rs.getString(3) + "]");
}
}catch (Exception e){
e.printStackTrace();
}finally {
try{
DBUtil.get_ClossConn(rs, pstm, conn);
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
3.使用JDBC向表中添加一个指定的数据(包含用户名和用户密码):
import java.sql.*;
import java.util.Scanner;
public class AddUser {
//测试添加
public static void add(User user){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
//1.获取连接
conn = DBUtil.get_Conn();
//2.获取存放sql语句的对象
pstm = conn.prepareStatement("insert into tb_user(username, password) values(?,?) ");
//3.填坑?
pstm.setString(1, user.getUsername());
pstm.setString(2, user.getPassword());
//4.执行sql并得到结果
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}catch (Exception e){
e.printStackTrace();
}finally {
//6.关闭连接
try{
DBUtil.get_ClossConn(null, pstm, conn);
}catch (SQLException e){
e.printStackTrace();
}
}
}
public static void addUser(){
System.out.println("请输入要添加的用户名及信息:");
Scanner in = new Scanner(System.in);
String name = in.next();
String password = in.next();
User user = new User(name, password);
add(user);
}
}
4.使用JDBC在输入用户名时查找表中该用户信息:
import java.sql.*;
import java.util.Scanner;
public class FindUser {
public static void find(String name){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("select * from tb_user");
rs = pstm.executeQuery();
while(rs.next()) {
if (rs.getString(2).equals(name)) {
System.out.println("[用户ID=" + rs.getInt(1) + ",用户名=" + rs.getString(2) + ",用户密码=" + rs.getString(3) + "]");
}
}
}catch (Exception e){
e.printStackTrace();
}finally {
try{
DBUtil.get_ClossConn(rs, pstm, conn);
}catch (SQLException e){
e.printStackTrace();
}
}
}
public static void findUser(){
System.out.println("请输入需要查找的用户名:");
Scanner in = new Scanner(System.in);
String name = in.next();
find(name);
}
}
5.使用JDBC在输入用户名时从表中删除该用户的信息:
import java.sql.*;
import java.util.Scanner;
public class DeleteUser {
public static void delete(String username){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("delete from tb_user where username=?");
pstm.setString(1, username);
//4.执行sql并得到结果
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}catch (Exception e){
e.printStackTrace();
}finally {
try{
DBUtil.get_ClossConn(null, pstm, conn);
}catch (SQLException e){
e.printStackTrace();
}
}
}
public static void deleteUser(){
System.out.println("请输入要删除的用户名:");
Scanner in = new Scanner(System.in);
String username = in.next();
delete(username);
}
}
6.使用JDBC在输入用户名时修改该用户的密码:
import java.sql.*;
import java.util.Scanner;
public class UpdateUser {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
public static void update(String username, String password){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("update tb_user set password=? where username=?");
pstm.setString(1, password);
pstm.setString(2, username);
//4.执行sql并得到结果
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}catch (Exception e){
e.printStackTrace();
}finally {
try{
DBUtil.get_ClossConn(null, pstm, conn);
}catch (SQLException e){
e.printStackTrace();
}
}
}
public static void updateUser(){
System.out.println("请输入要修改密码的用户名及修改后的密码:");
Scanner in = new Scanner(System.in);
String username = in.next();
String password = in.next();
update(username, password);
}
}
JDBC对数据库的一些简单的操作实现都在上面了,其中有一个地方要特别注意:
pstm.setString(1, user.getUsername());
pstm.setString(2, user.getPassword());
称它们为填坑的原因是这里的1和2是填补了
pstm = conn.prepareStatement("insert into tb_user(username, password) values(?,?) ");
里的两个问号,这也是个很好方法,灵活运用可以带来很多方便。