Java Web学习总结(九) JDBC

尖齿柠檬鲨

已于 2023-03-08 21:42:54 修改

阅读量610

点赞数

分类专栏： Java Web 文章标签： javaweb

于 2019-11-03 11:49:28 首次发布

本文链接：https://blog.csdn.net/teolih/article/details/102880876

版权

Java Web 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

Java Web学习总结(九) JDBC

什么是JDBC

JDBC的全称是Java数据库连接（Java Databse Connectivity)，它是一套用于执行SQL语句的Java API.

JDBC的一般步骤

加载驱动
连接数据库
执行SQL代码
1. 初始化SQL代码
2. 构建SQL代码
3. 执行SQL代码
关闭连接

示例：

创建示例数据库

CREATE DATABASE jc;
USE jc;
CREATE TABLE `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Id`)
)engine = InnoDB DEFAULT CHARSET=utf8;
USE jc;
insert into user (username, password) values('zs', '123');
insert into user (username, password) values('ls', '123');
insert into user (username, password) values('ww', '123');

创建实体类User

package ink.heat.entity;

public class User {
	private int id;
	private String username;
	private String password;
	public User() {}
	public User(String username, String password) {
		super();
		this.username = username;
		this.password = password;
	}
	public User(int id, String username, String password) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
	}
	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;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
	}
	
}

创建UserDao类（对User的增、删、改、查操作）

package ink.heat.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ink.heat.entity.User;

public class UserDao {
	/**
	 * 对User的增加， 删除， 修改， 查询
	 */
	private Connection conn = null;
	private PreparedStatement pstat = null;
	private ResultSet rs = null;
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/jc?characterEncoding=UTF-8";
	private String dbUsername = "root";
	private String dbPassword = "123456";
	
	/**
	 * 	获得数据库链接
	 * @return Connection 对象
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName(driver);//1.加载驱动
		return (Connection) DriverManager.getConnection(url, dbUsername, dbPassword);//2.获得数据库连接
	}
	
	/**
	 * 	关闭所有连接
	 */
	public void closeAll() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstat != null) {
			try {
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 	打印数据库返回的users
	 * @param users
	 */
	public void printUsers(List<User> users) {
		for (User user : users) {
			System.out.println(user);
		}
	}
	
	/**
	 * 	查询所有对象
	 * @return 所有用户对象
	 */
	public List<User> queryAllUser() {
		List<User> users = new ArrayList<User>();
		try{
			conn = getConnection();
			String sql = "select * from user";
			pstat = conn.prepareStatement(sql);//初始化SQL代码
			rs = pstat.executeQuery();//执行SQL代码
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				users.add(user);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return users;
	}
	
	/**
	 * 	增加一个User
	 * @param user
	 * @return
	 */
	public int addUser(User user) {
		int result = -1;
		try {
			conn = getConnection();
			String sql = "insert into user (username, password) values(?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, user.getUsername());
			pstat.setString(2, user.getPassword());
			result = pstat.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return result;
	}
	
	/**
	 * 	更新一个用户
	 * @param user
	 * @return
	 */
	public int updateUser(User user) {
		int result = -1;
		try {
			conn = getConnection();
			String sql = "update user set username=?,password=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, user.getUsername());
			pstat.setString(2, user.getPassword());
			pstat.setInt(3, user.getId());
			result = pstat.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return result;
	}
	
	/**
	 * 	删除一个用户
	 * @param id
	 * @return
	 */
	public int deleteUser(int id) {
		int result = -1;
		try {
			conn = getConnection();
			String sql = "delete from user where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			result = pstat.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return result;
	}
}

创建测试类

package ink.heat.test;
import java.util.List;

import ink.heat.dao.UserDao;
import ink.heat.entity.User;

public class JDBCTest {

	public static void main(String[] args) {
		
		List<User> users = null;
		UserDao dao = new UserDao();
		//查询
		System.out.println("--------【查询】- - - - - - -  ");
		users = dao.queryAllUser();
		dao.printUsers(users);
		
		
		
		System.out.println("--------【增加】- - - - - - -  ");
		User u = new User("zl", "123");
		dao.addUser(u);
		users = dao.queryAllUser();
		dao.printUsers(users);
		
		
		
		System.out.println("--------【修改】- - - - - - -  ");
		User u1 = new User(4, "zl", "1234");
		dao.updateUser(u1);
		users = dao.queryAllUser();
		dao.printUsers(users);
		
		
		
		System.out.println("--------【删除】- - - - - - -  ");
		dao.deleteUser(1);
		users = dao.queryAllUser();
		dao.printUsers(users);
	}

}

关于Statement和PreparedStatement

Statement 由于存在使用不方便以及存在SQL注入问题，所以不推荐使用。

创建DBUtils

上面的例子中，已经将重复的部分代码提取来作为方法了，但是这些方法都只对UserDao有效，将来项目Dao层多时，代码又会重复，所以将它们提取到一个工具类是十分有必要的。

package ink.heat.util;

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 DBUtils {
	private static String driver;
	private static String url;
	private static String dbUsername;
	private static String dbPassword;
	
	static {
		Properties properties = new Properties();
		InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			properties.load(in);
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			dbUsername = properties.getProperty("dbUsername");
			dbPassword = properties.getProperty("dbPassword");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName(driver);
		return (Connection) DriverManager.getConnection(url, dbUsername, dbPassword);
	}
	
	public static void closeAll(Connection conn, Statement stat, ResultSet rs) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

在src目录下新建db.properties文件，文件内容如下：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jc?characterEncoding=UTF-8
dbUsername=root
dbPassword=123456

总结

最后，将UserDao结合DBUtils，并加以简化至如下：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static run.heat.utils.DBUtil.getConnection;

public class UserDao {

/**
 * 打印数据库返回的users
 */
public void printUsers(List<User> users) {
    users.forEach(System.out::println);
}

/**
 * 查询所有对象
 *
 * @return 所有用户对象
 */
public List<User> queryAllUser() {
    List<User> users = new ArrayList<User>();
    final String sql = "select * from user";
    try (Connection conn = getConnection();
         PreparedStatement pstat = conn.prepareStatement(sql);
         ResultSet rs = pstat.executeQuery();) {
        while (rs.next()) {
            User user = User.builder()
                    .id(rs.getInt("id"))
                    .username(rs.getString("username"))
                    .password(rs.getString("password"))
                    .build();
            users.add(user);
        }
    } catch (SQLException | ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
    return users;
}

/**
 * 增加一个User
 */
public int addUser(User user) {
    int result = -1;
    final String sql = "insert into user (username, password) values(?,?)";
    try (Connection conn = getConnection();
         PreparedStatement pstat = conn.prepareStatement(sql);) {
        pstat.setString(1, user.getUsername());
        pstat.setString(2, user.getPassword());
        result = pstat.executeUpdate();
    } catch (SQLException | ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
    return result;
}

/**
 * 更新一个用户
 */
public int updateUser(User user) {
    int result = -1;
    final String sql = "update user set username=?,password=? where id=?";
    try (Connection conn = getConnection();
         PreparedStatement pstat = conn.prepareStatement(sql)) {
        if (user.getId() > 0) {
            pstat.setString(1, user.getUsername());
            pstat.setString(2, user.getPassword());
            pstat.setInt(3, user.getId());
            result = pstat.executeUpdate();
        }
    } catch (SQLException | ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
    return result;
}

/**
 * 删除一个用户
 */
public int deleteUser(int id) {
    int result = -1;
    final String sql = "delete from user where id=?";
    try (Connection conn = getConnection();
         PreparedStatement pstat = conn.prepareStatement(sql);) {
        pstat.setInt(1, id);
        result = pstat.executeUpdate();
    } catch (SQLException | ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
    return result;
}

}

```

尖齿柠檬鲨

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Java Web学习总结(九) JDBC

Java Web学习总结(九) JDBC什么是JDBCJDBC的全称是Java数据库连接（Java Databse Connectivity)，它是一套用于执行SQL语句的Java API.JDBC的一般步骤加载驱动连接数据库执行SQL代码初始化SQL代码构建SQL代码执行SQL代码关闭连接示例：创建示例数据库CREATE DATA...
复制链接

扫一扫