[大数据]JDBC-1026-day01

JDBC(Java DataBase Connectivity) Java数据库连接

1，注册数据库驱动

Class.forName("路径.Driver");

2，获取数据库连接

Connection conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/hc_db?characterEncoding=utf8",

"账户名是啥写啥", "密码");

3，获取传输器

Statement stat = conn.createStatement();

4，发送SQL到服务器执行并返回执行结果

String sql = "SELECT * FROM account";

ResultSet rs = stat.executeQuery(sql);

5,处理结果

while(rs.next()){

Integer id = rs.getInt("id");

String name = rs.getString("name");

Double salary = rs.getDouble("money");

System.out.println(id + ", " + name + ", " + salary);

}

6，释放资源

rs.close();

stat.close();

conn.close();

System.out.println("释放资源完毕！");

代码如下

1、新增:往account表中添加一个名称为王飞、money为10000的记录

2、修改:将account表中名称为wmm的记录，money修改为15000

3、查询:查询account表中名称为wmm的记录

4、删除:删除account表中名称为"王飞"的记录

 

/**
 * @author RogerJiang
 * 2021/10/26 14:41
 */
public class JDBCDemo02 {
    public static void main(String[] args) {
        testInsert();
        testUpdate();
        testAccount();
        testDelet();
    }




    public static void testInsert() {
        Connection connection =null;
        Statement statement =null;
        try {

            connection = JDBCUtil.getConnection();
           statement =connection.createStatement();
            String sql ="insert into account values(null,'王飞',10000.0)";
        int rows=    statement.executeUpdate(sql);
        if (rows ==1){
            System.out.println("数据保存成功");
        }else{
            System.out.println("保存失败");
        }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            if (statement !=null){
//                try {
//                    statement.close();
//                } catch (SQLException throwables) {
//                    throwables.printStackTrace();
//                }
//            }
//            if (connection !=null){
//                try {
//                    connection.close();
//                } catch (SQLException throwables) {
//                    throwables.printStackTrace();
//                }
//            }
            JDBCUtil.close(null,statement,connection);
        }
    }

    public static void testUpdate() {
        Connection connection=null;
        Statement  statement =null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/hc_db?characterEncoding=utf8",
                    "root","1234"
            );
            String sql=" update  account set money=15000 where name='wmm'";
            statement=connection.createStatement();
            int rows=statement.executeUpdate(sql);
            System.out.println("修改数据条数"+rows);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败");
            e.printStackTrace();
        } catch (SQLException throwables) {
            System.out.println("创建传送器失败，或者sql语法错误");
            throwables.printStackTrace();
        }finally {
if (statement !=null){
    try {
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

if (connection !=null){
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}
        }
    }
    public static void testAccount() {
        Connection connection=null;
        Statement statement =null;
        ResultSet resultSet=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hc_db?characterEncoding=utf8",
                    "root","1234"
            );
            statement =connection.createStatement();
            String sql="select *from account where name ='wmm'";
            resultSet=statement.executeQuery(sql);
            if (resultSet.next()){
                Integer id =resultSet.getInt(1);
                String name=resultSet.getString(2);
                Double money=resultSet.getDouble(3);
                System.out.println(id+','+name+','+money);
            }
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败");
            e.printStackTrace();
        } catch (SQLException throwables) {
            System.out.println("创建传送器失败，或者sql语法错误");
            throwables.printStackTrace();
        }finally {
            if (resultSet !=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement !=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
if (connection !=null){
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}
        }
    }
    public static void testDelet() {
        Connection connection=null;
        Statement statement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/hc_db?characterEncoding=utf8",
                    "root","1234");
            String sql="delete from account where name='王飞'";
            statement= connection.createStatement();
            int rows=statement.executeUpdate(sql);
            System.out.println("修改条数为："+rows);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败");
            e.printStackTrace();
        } catch (SQLException throwables) {
            System.out.println("创建传输器失败，或者语法错误");
            throwables.printStackTrace();
        }finally {
            if (statement !=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection !=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }
    }
}