jdbc原生sql连接数据库(最基本的)

电玩女神 2024-04-19 10:22 54阅读 0赞

## jdbc原生sql连接数据库(最基本的) ##

jdbc.properties文件

url=jdbc:mysql://localhost:3306/employee?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
    username=root
    password=root
    driver=com.mysql.cj.jdbc.Driver

JDBCUtil.java

package com.zr.utils;
    
    import java.io.FileNotFoundException;
    import java.io.FileReader;
    import java.io.IOException;
    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 ZR
     *
     */
    public class JDBCUtil {
    	private static String url;
    	private static String user;
    	private static String password;
    	private static String driver;
    	
    	/**
    	 * 使用静态代码块获取properties文件的数据
    	 */
    	static {
    		Properties properties=new Properties();
    		try {
    			properties.load(new FileReader("src/jdbc.properties"));
    			url=properties.getProperty("url");
    			user=properties.getProperty("username");
    			password=properties.getProperty("password");
    			driver=properties.getProperty("driver");
    		} catch (FileNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	
    	/**
    	 * 获取数据库链接
    	 * @return
    	 */
    	public static Connection getConnection() {
    		try {
    			Class.forName(driver);
    			return DriverManager.getConnection(url,user,password);
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return null;
    	}
    	
    	/**
    	 * 关闭数据库链接,一定要按ResultSet,Statement,Connection前后顺序关闭
    	 * @param connection
    	 * @param statement
    	 * @param resultSet
    	 */
    	public static void close(Connection connection,Statement statement,ResultSet resultSet) {
    		if (resultSet!=null) {
    			try {
    				resultSet.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (statement!=null) {
    			try {
    				statement.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (connection!=null) {
    			try {
    				connection.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    	}
    }

JDBCDemo1.java

package com.zr.demo;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import com.zr.utils.JDBCUtil;
    
    public class JDBCDemo1 {
    
    	public static void main(String[] args) {
    		//连接数据库
    		Connection connection = JDBCUtil.getConnection();
    		//PreparedStatement是Statement的子类,PreparedStatement比Statement安全,效率高,防止sql注入
    		PreparedStatement preparedStatement =null;
    		ResultSet resultSet =null;
    		//sql语句
    		String sql="select * from dept where dname=? and loc=?";
    		try {
    			preparedStatement = connection.prepareStatement(sql);
    			//给问号赋值
    			preparedStatement.setString(1, "编译");
    			preparedStatement.setString(2, "龙谷");
    			
    			//查询
    			resultSet = preparedStatement.executeQuery();
    			
    			//按表格输出到控制台
    			System.out.println("deptno\tdname\t\tloc");
    			while(resultSet.next()) {
    				int deptno=resultSet.getInt(1);
    				String dname=resultSet.getString(2);
    				String loc=resultSet.getString(3);
    				System.out.println(deptno+"\t"+dname+"\t\t"+loc);
    			}
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally {
    			//关闭连接,释放资源
    			JDBCUtil.close(connection, preparedStatement, resultSet);
    		}
    
    	}
    
    }