JAVA EE之JDBC

布满荆棘的人生 2022-05-09 03:04 234阅读 0赞

# **一: JDBC的基本用法** #

### 1：创建数据库连接对象 ###

1 在工程中导入第三方的jar文件。（数据库驱动类）

![70][]

2 加载驱动类

Class.forName("com.mysql.jdbc.Driver");//类加载。

3 创建数据库连接对象

String url ="jdbc:mysql://127.0.0.1:3306/heda?useUnicode=true&characterEncoding=utf8";

Connection conn = DriverManager.getConnection(url , "root", "root");

### 2：CURD操作 ###

String sql = "select \* from users";  
// PreparedStatement对象是操作对象 ，负责向数据库发送SQL语句。  
PreparedStatement pstat = conn.prepareStatement(sql);  
pstat.setString(1, "15810848579");  
// ResultSet是结果集对象 ，负责接收查询结果.  
ResultSet rs = pstat.executeQuery();  
while (rs.next()) \{  
String userName = rs.getString("user\_name");  
String password = rs.getString("user\_pass");  
String userImg = rs.getString("user\_img");  
System.out.println(userName);  
System.out.println(password);  
System.out.println(userImg);

### **3：关闭数据库连接对象** ###

conn.close();

**完整代码：**

package henu;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Main {
    
    	public static void main(String[] args) {
    		Connection conn = null;
    		try {
    			// 第一步：创建数据库连接对象
    			Class.forName("com.mysql.jdbc.Driver");// 类加载。
    			String url = "jdbc:mysql://127.0.0.1:3306/dbcp?useUnicode=true&characterEncoding=utf8";
    			// Connection是连接对象 ，负责与数据库创建物理连接。//dbcp是我数据库名字
    			conn = DriverManager.getConnection(url, "root", "root");
    			// 第二步：CRUD操作
    			String sql = "select * from users";
    			// PreparedStatement对象是操作对象 ，负责向数据库发送SQL语句。
    			PreparedStatement pstat = conn.prepareStatement(sql);
    			// pstat.setString(1, "15810848579");
    			// ResultSet是结果集对象 ，负责接收查询结果.BOF,EOF
    			ResultSet rs = pstat.executeQuery();
    			while (rs.next()) {
    				String userName = rs.getString("USER_NAME");
    				String password = rs.getString("USER_PASS");
    				String userImg = rs.getString("USER_IMG");
    				System.out.println(userName);
    				System.out.println(password);
    				System.out.println(userImg);
    				System.out.println("--------------");
    			}
    		} catch (ClassNotFoundException | SQLException e) {
    			e.printStackTrace();
    		} finally {
    			// 第三步：关闭数据库连接对象
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    
    }

# 二：JDBC的简单封装  #

### 1：封装JDBC的操作类 ###

package com.fish.utils;
    
    
    import java.sql.*;
    
    //数据库操作公共类
    public class ConnectUtil {
        private ConnectUtil(){}
    
        private static  String url="jdbc:mysql://localhost:3306/FISH?useUnicode=true&characterEncoding=utf-8&useSSL=true";
        private static  String user="root";
        private static  String password="root";
    
    
        //       1：加载驱动
        static {
            try {
    
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                System.out.println("找不到驱动类");
                e.printStackTrace();
            }
        }
    
        //        2：连接
        public static Connection getConnection(){
    
            Connection conn= null;
            try {
                conn = DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            return conn;
        }
    
    
        //       3：释放资源
        public static void  release(ResultSet rs, PreparedStatement stmt ,Connection conn){
    
                try {
                    if(rs!=null) {
                        rs.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    try {
                        if(stmt!=null) {
                            stmt.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        try {
                            if(conn!=null) {
                                conn.close();
                            }
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                   }
                }
    
        }
    
    
    
    }

### 2：封装类的调用 ###

public class UserDao {
        /**
         * 前台用户登录
         * @param username 用户名
         * @param password 密码
         * @return 成功返回数据库用户实体 失败返回null
         */
        public User login(String username, String password){
    
            Connection conn=ConnectUtil.getConnection();
            String sql="select * from users where username= ? and password=?";
            PreparedStatement stmt=null;
            ResultSet rs=null;
            User user=null;
    
            try {
                stmt=conn.prepareStatement(sql);
                stmt.setString(1,username);
                stmt.setString(2,password);
                rs=stmt.executeQuery();
                //如果有这个用户，则从数据库取出用户信息并返回这个用户信息
                while (rs.next()){
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setEmail(rs.getString("email"));
                }
            } catch (SQLException e) {
                System.out.println("UserDao-----------------------------登录失败");
                e.printStackTrace();
                return null;
            }finally {
                ConnectUtil.release(rs,stmt,conn);
            }
    
    
            return  user;
        }
    }

# 三：JDBC属性文件的抽取 #

### 1：在src目录下新建jdbc.properties属性文件 ###

driverClass=com.mysql.jdbc.Driver
    url=jdbc:mysql:///jdbcdemo
    username=root
    password=root

### 2：封装JDBC的操作类 ###

package com.imooc.jdbc.utils;
    
    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;
    
    /**
     * JDBC的工具类
     * @author jt
     *
     */
    public class JDBCUtils {
    	private static final String driverClass;
    	private static final String url;
    	private static final String username;
    	private static final String password;
    	
    	static{
    		// 加载属性文件并解析：
    		Properties props = new Properties();
    		// 如何获得属性文件的输入流？
    		// 通常情况下使用类的加载器的方式进行获取：
    		InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
    		try {
    			props.load(is);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		
    		driverClass = props.getProperty("driverClass");
    		url = props.getProperty("url");
    		username = props.getProperty("username");
    		password = props.getProperty("password");
    	}
    
    	/**
    	 * 注册驱动的方法
    	 * @throws ClassNotFoundException 
    	 */
    	public static void loadDriver() throws ClassNotFoundException{
    		Class.forName(driverClass);
    	}
    	
    	/**
    	 * 获得连接的方法:
    	 * @throws SQLException 
    	 */
    	public static Connection getConnection() throws Exception{
    		loadDriver();
    		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;
    		}
    	}
    
    	/**
    	 * 有结果集的资源释放
    	 * @param rs
    	 * @param stmt
    	 * @param conn
    	 */
    	public static void release(ResultSet rs,Statement stmt,Connection conn){
    		if(rs!= null){
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    			rs = null;
    		}
    		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;
    		}
    	}
    }

### 3：封装类的调用 ###

public class JDBCDemo4 {
    
    	
    	public static boolean login2(String username,String password){
    		Connection conn = null;
    		PreparedStatement pstmt = null;
    		ResultSet rs = null;
    		boolean flag = false;
    		try{
    			// 获得连接:
    			conn = JDBCUtils.getConnection();
    			// 编写SQL:
    			String sql = "select * from user where username = ? and password = ?";
    			// 预处理SQL:
    			pstmt = conn.prepareStatement(sql);
    			// 设置参数:
    			pstmt.setString(1, username);
    			pstmt.setString(2, password);
    			// 执行SQL：
    			rs = pstmt.executeQuery();
    			// 判断结果
    			if(rs.next()){
    				flag = true;
    			}else{
    				flag = false;
    			}
    		}catch(Exception e){
    			e.printStackTrace();
    		}finally{
    			JDBCUtils.release(rs, pstmt, conn);
    		}
    		return flag;
    	}
    }

[70]: /images/20220509/2fa1bbae3b6d418fa22c6c4b84176efa.png