PHP 程序员学习 java 入门数据库增删改查

数据库增删改查

java 实现数据库的操作，完成数据库增删改查。

实现流程

• 1.创建数据库驱动
• 2.创建数据库连接
• 3.编写SQL语句
• 4.执行SQL语句，完成操作

创建一个数据库配置

文件放置于根目录下config/db.properties

jdbc.user=root
jdbc.pass=123456
jdbc.url=jdbc:mysql://127.0.0.1:3306/b?useSSL=FALSE&serverTimezone=UTC
jdbc.driver=com.mysql.cj.jdbc.Driver

读取操作类PropsUtil

package com.kongqi.mysql;

import java.io.IOException;
import java.util.Properties;

/**
 * 读取配置文件数据
 */
public class PropsUtil {
    private static Properties properties;
    private PropsUtil(){

    };
    static {
        properties=new Properties();
        //加载文件
        try {
            properties.load(PropsUtil.class.getClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static String getValue(String key){
        return properties.getProperty(key);
    }
}

驱动和连接数据库

数据库帮助类

package com.kongqi.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbHelper {
    private DbHelper() {
    }

    private static String url;
    private static String user;
    private static String passwrod;
    private static String driver;

    private static final ThreadLocal<Connection> conn=new ThreadLocal<>();

    static {
        url = PropsUtil.getValue("jdbc.url");
        user = PropsUtil.getValue("jdbc.user");
        passwrod = PropsUtil.getValue("jdbc.pass");
        driver = PropsUtil.getValue("jdbc.driver");
        //注册驱动
        try {
            Class.forName(driver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public synchronized static Connection getMysqlConnection() {
        //
        Connection connection=conn.get();

        if(connection==null){
            try {
                connection=DriverManager.getConnection(url,
                        user,
                        passwrod);
                conn.set(connection);

            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("数据库连接不上");
            }

        }
        return  connection;

    }

    public static void closeResources(Connection connection) {

        try {
            if (connection != null) {
                connection.close();
                conn.remove();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

实现增加数据

先创建createStatement,再来实现executeUpdate 完成SQL的输出

 Connection connection=DbHelper.getMysqlConnection();
//添加
String sql5="insert into users(name,token,email,password) values('kdfad','e3rewrerewr','dd@qq.com','123245')";
try {
        Statement stm=connection.createStatement();
        int result=stm.executeUpdate(sql5);
        System.out.println("有"+result+"行记录添加");
} catch (SQLException throwables) {
        throwables.printStackTrace();
}

实现更新数据

先创建createStatement,再来实现executeUpdate 完成SQL的输出

String sql3="update users set name='kongqierer'where id=2";
try {
        Statement stm=connection.createStatement();
        int result=stm.executeUpdate(sql3);
        System.out.println("有"+result+"行记录被修改");
} catch (SQLException throwables) {
        throwables.printStackTrace();
}

实现删除数据

先创建createStatement,再来实现executeUpdate 完成SQL的输出

//删除
String sql4="delete from users  where id=2";
try {
        Statement stm=connection.createStatement();
        int result=stm.executeUpdate(sql4);
        System.out.println("有"+result+"行记录被被删除");
} catch (SQLException throwables) {
        throwables.printStackTrace();
}

查询

不带参数查询
先创建Statement 接口，再来执行executeQuery

try {
        Statement st =  connection.createStatement();
        ResultSet rs =  st.executeQuery("select*from users");
         while (rs.next()){
                    System.out.println(rs.getString("name"));
            }
} catch (SQLException throwables) {
        throwables.printStackTrace();
}

带查询参数

创建 PreparedStatement 接口，再来执行executeQuery

String sql2="select * from users where id=?";
    try {
            PreparedStatement ps=connection.prepareStatement(sql2);
            ps.setString(1,"2");
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                    System.out.println(rs.getString("name"));
            }

    } catch (SQLException throwables) {
            throwables.printStackTrace();
    }

获得新增id

 Connection connection=DbHelper.getMysqlConnection();
//添加
String sql5="insert into users(name,token,email,password) values('kdfad','e3rewrerewr','dd@qq.com','123245')";
try {
        Statement stm=connection.createStatement();
        int result=stm.executeUpdate(sql5);
        System.out.println("有"+result+"行记录添加");
        ResultSet rs=stm.getGeneratedKeys();
        if(rs.next()){
                System.out.println(rs.getLong(1));
        }
} catch (SQLException throwables) {
        throwables.printStackTrace();
}

事务操作

Connection connection=DbHelper.getMysqlConnection();
        //添加
        String sql5="insert into users(name,token,email,password) values('kdfad','e3rewrerewr','dd3323@qq.com','123245')";

        try {
            connection.setAutoCommit(false);//关闭自动事务

            Statement stm=connection.createStatement();
            int result=stm.executeUpdate(sql5,1);

            System.out.println("有"+result+"行记录添加");
            ResultSet rs=stm.getGeneratedKeys();
            if(rs.next()){
                System.out.println(rs.getLong(1));
            }
            connection.commit();//提交事务

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }catch (Exception e){
            try {
                connection.rollback();//回滚
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }