JDBC核心技术

忆丶长安

已于 2022-07-28 10:26:52 修改

阅读量173

点赞数 1

文章标签：数据库 sql java

于 2022-07-28 10:16:24 首次发布

本文链接：https://blog.csdn.net/vn666/article/details/126028542

版权

java连接数据库的步骤:

1:加载驱动

2:获得连接对象

3.获得sql对象

4.执行sql语句方法

5.处理sql语句结果

6.释放资源

使用查询的举例:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class QueryJdbc {
    public static void main(String[] args) throws Exception{
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mystudent", "root", "password");
        //3.获取sql对象
        Statement statement = connection.createStatement();
        //4.执行sql方法
        ResultSet resultSet = statement.executeQuery("select * from student");
        //5.处理结果
        while(resultSet.next()){//判断下一句是否有数据
            String sid=resultSet.getString(1);
            String name=resultSet.getString(2);
            String sex = resultSet.getString(3);
            System.out.println(sid+"\t"+name+"\t"+sex+"\t");
        }
        //6.释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

使用登陆的举例:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

/**
 * 从数据库中获取数据判断用户登陆
 */
public class LoginJdbc {
    public static void main(String[] args) throws Exception{
        Scanner input=new Scanner(System.in);
        System.out.println("请输入用户名: ");
        String username = input.next();
        System.out.println("请输入密码 ");
        String password = input.next();

        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mystudent","root","password");

        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery("select *from user where username='"+username+"' and password='"+password+"'");

        if (resultSet.next()){
            System.out.println("登陆成功!");
        }else {
            System.out.println("登陆失败!");
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

防止sql注入:

在登陆的时候我们会输入用户名,但是当用户名中存在影响sql语句的关键词或者符号时,这会改变sql语句执行的结果,因此,我们应该防止sql注入的问题

解决方法:

使用PreparedStatement来执行sql语句,并将输入的或者要查询的部分使用占位符"?"代替

db.properties文件

dirver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mystudent?useUnicode&characterEncoding=utf8
username=root
password=password

对重复的方法进行封装

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtils {

    private static final Properties PROPERTIES =new Properties();//储存配置文件的map

    static {
        InputStream is =DBUtils.class.getResourceAsStream("/db.properties");//读取properties文件
        try {
            PROPERTIES.load(is);//通过流,将配置文件的内容加载到properties集合中,已键值对的形式
            Class.forName(PROPERTIES.getProperty("dirver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }


    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet!=null){
                resultSet.close();
            }
            if (statement!=null){
                statement.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Person类:

import java.util.Date;

/**
 * 进行对象封装
 */
public class Person {
    private int id;
    private String name;//姓名
    private int age;//年龄
    private Date bornDate;//出生日期
    private String email;//邮箱
    private String address;//住址

    public Person() {
    }

    public Person(String name, int age, Date bornDate, String email, String address) {
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }

    public Person(int id, String name, int age, Date bornDate, String email, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bornDate=" + bornDate +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBornDate() {
        return bornDate;
    }

    public void setBornDate(Date bornDate) {
        this.bornDate = bornDate;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

PersonDapImpl类(实现具体的增,删,改,查):

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

public class PersonDaoImpl {


    //新增
    public int insert(Person person){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection=DBUtils.getConnection();
            preparedStatement= connection.prepareStatement("insert into person (name,age,borndate,email,address) values (?,?,?,?,?)");
            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }

    //修改
    public int update(Person person){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection=DBUtils.getConnection();
            preparedStatement=connection.prepareStatement("update person set name=?,age=?,bornDate=?,email=?,address=? where id=?");
            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());
            preparedStatement.setInt(6,person.getId());
            int result = preparedStatement.executeUpdate();
            return result;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }

    //删除文件
    public int delete(int id){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection= DBUtils.getConnection();
            preparedStatement=connection.prepareStatement("delete from person where id=?");
            preparedStatement.setInt(1,id);
            int result = preparedStatement.executeUpdate();
            return result;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }

    //查询单个文件
    public Person select(int id){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        String sql="select * from person where id=?";
        Person person=null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                //通过数据库列属性获得数据
               id= resultSet.getInt("id");
               String name=resultSet.getString("name");
               int age=resultSet.getInt("age");
               Date bornDate=resultSet.getDate("bornDate");
               String email=resultSet.getString("email");
               String address=resultSet.getString("address");
               person=new Person(id,name,age,bornDate,email,address);
               return person;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    //查询所有文件
    public List<Person> selectAll(){
        String sql="select * from person";
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<Person> personList= new ArrayList<>();

        try {
            connection=DBUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //获取对象数据
                int id= resultSet.getInt("id");
                String name=resultSet.getString("name");
                int age=resultSet.getInt("age");
                Date bornDate=resultSet.getDate("bornDate");
                String email=resultSet.getString("email");
                String address=resultSet.getString("address");
                //进行数据封装
                Person person=new Person(id,name,age,bornDate,email,address);
                //数据储存带集合中
                personList.add(person);
            }
            return personList;
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
}

测试类:

public class TestJdbc {
    public static void main(String[] args) {

        //写入数据
       /*Person person=new Person("小明",10,null,"465456@qq.com","地球");
        PersonDaoImpl personDao=new PersonDaoImpl();
        int result = personDao.insert(person);
        if(result==1){
            System.out.println("成功写入数据");
        }else {
            System.out.println("写入数据失败");
        }*/

        //修改文件
        /*Person person=new Person(4,"李四",18,null,"123456789@qq.com","合肥市庐阳区");
        PersonDaoImpl personDao=new PersonDaoImpl();
        int result = personDao.update(person);
        if (result==1) {
            System.out.println("成功修改文件!");
        }else {
            System.out.println("修改文件失败!");
        }*/

        //删除数据
        /*PersonDaoImpl personDao=new PersonDaoImpl();
        int result = personDao.delete(3);
        if (result==1) {
            System.out.println("成功删除文件!");
        }else {
            System.out.println("删除失败!");
        }*/

        //查询单个数据
        /*PersonDaoImpl personDao=new PersonDaoImpl();
        Person person = personDao.select(5);
        System.out.println(person);*/

        //查询所有数据
        PersonDaoImpl personDao=new PersonDaoImpl();
        List<Person> list = personDao.selectAll();
        Iterator iterator=list.iterator();
        while (iterator.hasNext()){
            Person person = (Person) iterator.next();
            System.out.println(person);
        }
    }
}