JDBC是一个Java应用程序接口,作用是封装了对数据库的各种操作。JDBC由类和接口组成,使用Java开发数据库应用都需要4个主要的接口:Driver、Connection、Statement、ResultSet,这些接口定义了使用SQL访问数据库的一般架构。
JDBC七步走
- 加载驱动
Class.forName(driverName);
- 指定用户名和密码
Connection conn = DriverManager.getConnection(url,user,pwd);
- 获取连接
if (!conn.isClosed()) {
System.out.println("###Connection successful!###");
}
- 创建一个Statement对象
Statement stmt = conn.createStatement();
- 执行sql语句
ResultSet rs = stmt.executeQuery(sql_query);
- 展示结果集
while (rs.next()) {
System.out.println(rs.getString("id") + " | " + rs.getString("name"));
}
- 关闭对象
rs.close();
stmt.close();
conn.close();
重写Dao工具类
- 通过Dao更快捷的的操作数据库
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.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @desc 利用JDBC重构获取一个Dao工具类
* @author 86176
* @time 2021-05-23
*/
public class Dao {
// -------连接四要素
private String driverName = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://127.0.0.1:3307/test20210523_51?useUnicode=true&characterEncoding=utf-8";
private String user = "root";
private String pwd = "root";
/**
* @desc 1.获取连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConnection() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName(driverName);
// 指定URL/user+pwd
Connection conn = DriverManager.getConnection(url, user, pwd);
return conn;
}
/**
* @desc 2.释放连接
* @param conn
* @param stmt
* @param rs
* @throws SQLException
*/
public void releaseConnection(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
System.out.println("###关闭连接:releaseConnection");
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
private void releaseConnection(Connection conn, Statement stmt) throws SQLException {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
/**
* @desc 3.查询全部
* @param sql
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public List<Map<String, Object>> executeQueryForList(String sql) throws ClassNotFoundException, SQLException {
System.out.println("查询全部--sql--" + sql);
Connection conn = this.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Map<String, Object>> list = rsToList(rs);
this.releaseConnection(conn, stmt, rs);
return list;
}
/**
* @desc 4.将rs结果集转变为List<map>
* @param rs
* @return
* @throws SQLException
*/
private List<Map<String, Object>> rsToList(ResultSet rs) throws SQLException {
List<Map<String, Object>> rowsList = new ArrayList<Map<String, Object>>();
// System.out.println(rs.getMetaData().getColumnCount()+"rows");
while (rs.next()) // 控制循环行
{
Map<String, Object> colsMap = new HashMap<String, Object>();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) // 内部循环控制列
{
switch (rs.getMetaData().getColumnType(i)) {
case Types.VARCHAR:
colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i));
break;
case Types.INTEGER:
colsMap.put(rs.getMetaData().getColumnName(i), rs.getInt(i));
break;
case Types.BLOB: // 图片类型
InputStream in = rs.getBinaryStream(i);
colsMap.put(rs.getMetaData().getSchemaName(i), in);
break;
default:
colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i));
break;
}
}
rowsList.add(colsMap);
}
return rowsList;
}
/**
* @desc 5.查询一条
* @param sql
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public Map<String, Object> executeQueryForMap(String sql) throws SQLException, ClassNotFoundException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
System.out.println("查询一条--sql--" + sql);
try {
conn = this.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
List<Map<String, Object>> list = this.rsToList(rs);
if (!list.isEmpty()) {
return list.get(0);
}
} finally {
this.releaseConnection(conn, stmt, rs);
}
return null;
}
/**
* @desc 6.查询一共有多少条
* @param sql
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public int executeQueryForCount(String sql) throws ClassNotFoundException, SQLException {
System.out.println("查询一共有多少条的sql:" + sql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = this.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
return rs.getInt(1);
}
} finally {
this.releaseConnection(conn, stmt, rs);
}
return 0;
}
/**
* 7.执行增删改操作
* @param sql
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
System.out.println("增删改:" + sql);
Connection conn = this.getConnection();
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
this.releaseConnection(conn, stmt);
return count;
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Dao dao = new Dao();
/*模糊搜索 select*from user where id like '%3'
*查询全部 select*from user
*查询个数 select count(*) from user
*select*from user where id not in (004,003)
*/
System.out.println(dao.executeQueryForList("select*from user where id not in (004,003)"));
// System.out.println(dao.executeQueryForMap("select*from user where id='003'"));
// System.out.println(dao.executeQueryForCount("select count(*) from user"));
}
}