JDBC java数据库连接

 

 

 

 

 

 

 

 

 

package JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCDemo01 {

    public List<Emp> findAll() throws SQLException {

        ResultSet r = null;
        Statement s = null;
        Connection root = null;
        List<Emp> emp = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
            root = DriverManager.getConnection("jdbc:mysql://localhost/pracice", "root", "123456");
            //sql语句
            String sql = "select * from user";
            //获取执行sql的对象
            s = root.createStatement();
            //执行sql
            r = s.executeQuery(sql);
            Emp e = new Emp();
            while (r.next()){
                int id = r.getInt("id");
                String name = r.getString("name");
                e.setId(45);
                e.setName("xiaom");
                emp.add(e);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        if(r != null){

            r.close();
        }
        if(s != null){
            r.close();
        }
        if(root != null){
            r.close();
        }

        return emp;
    }

    public static void main(String[] args) throws SQLException {
        List<Emp> list = new JDBCDemo01().findAll();
        System.out.println(list);
    }


}

package JDBC;

public class Emp {
    private int id;
    private String name;

    public Emp() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Emp(int id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

 结果：

 由于上面的代码重复度过于太高，所以我们将对其进行简写

package util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/*
*
* JDBC工具栏
* */
public class JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String Driver;
    /*
    * 文件的读取只需要一次就可以拿到这些值，所以我们看呀选择静态代码块来解决
    * */
    static {

        //读取资源文件，获取值
        //创建Properties集合类
        Properties pro = new Properties();
        //加载文件
        try {
            //静态代码块只能 try catch 处理异常，不能用 throw 抛出异常
            //1.可以写绝对路径
            //2.获取src路径下的文件--->ClassLoader 类加载器
            ClassLoader cL = JDBCUtils.class.getClassLoader();
            URL res = cL.getResource("jdbc.properties");
            String path = res.getPath();
            pro.load(new FileReader(path));
            //获取资源
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
          /*  
            //放入注册驱动就不能用 
            //Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class util.JDBCUtils
            Driver = pro.getProperty("Driver");
            //注册驱动
            Class.forName(Driver);*/
        } catch (IOException e) {
            e.printStackTrace();
        } /*catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/
    }
    //获取连接对象
    public static Connection getConnention() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //释放资源
    public static void close(Statement s, Connection c){
        if(s != null){
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(c != null){
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        }
    //释放资源 方法重载
    public static void close(ResultSet r,Statement s, Connection c){
        if(s != null){
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(c != null){
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(r != null){
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package JDBC;



import util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCDemo01 {

    public List<Emp> findAll() throws SQLException, ClassNotFoundException {

        ResultSet r = null;
        Statement s = null;
        Connection c = null;
        List<Emp> emp = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            c = JDBCUtils.getConnention();
            //sql语句
            String sql = "select * from user";
            //获取执行sql的对象
            s = c.createStatement();
            //执行sql
            r = s.executeQuery(sql);
            Emp e = new Emp();
            while (r.next()) {
                int id = r.getInt("id");
                String name = r.getString("name");
                e.setId(45);
                e.setName("xiaom");
                emp.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(r,s,c);
        }
        return emp;
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        List<Emp> list = new JDBCDemo01().findAll();
        System.out.println(list);
    }


}

url=jdbc:mysql://localhost/pracice
user=root
password=123456
driver=com.mysql.jdbc.Driver

练习：

键盘输入用户名以及密码 判断正确以及错误

 

 注意：

ps.setInt(1,user);
ps.setString(2,password);
r = ps.executeQuery();
setXxx（）方法要在executeQuery前面！！！

package JDBC;

import util.JDBCUtils;

import java.sql.*;
import java.util.Scanner;

public class JDBCDemo02 {
    public static void main(String[] args) throws ClassNotFoundException {

        Connection c = null;
        ResultSet r = null;
     //   Statement s = null;
        PreparedStatement ps = null;
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //键盘输入
        System.out.println("请输入你的用户名");
        Scanner sc = new Scanner(System.in);
        int user = sc.nextInt();

        System.out.println("请输入你的密码");
        Scanner sc1 = new Scanner(System.in);
        String password = sc1.nextLine();

        if(user == 0 && password == null){
            System.out.println("失败");
        }
        try {
            //
            c = JDBCUtils.getConnention();
            String user_sql = "select id from user";
            //获取执行sql的对象
           // ps = c.createStatement();
            //执行sql
            //用prepareStatement可以避免sql注入问题 'a' or 'a' = 'a'  按照逻辑为true
           // r = s.executeQuery("select * from user where id=" + user + " and name=" + password );
            String sql = "select * from user where id=? and name=?";
            ps = c.prepareStatement(sql);
            //r = ps.executeQuery();
            ps.setInt(1,user);
            ps.setString(2,password);
            r = ps.executeQuery();
            if(r.next()){
                System.out.println("成功");
            }else{
                System.out.println("失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.close(r,ps,c);
        }


    }
}