JavaWeb5.1【JDBC：基本概念、快速入门】

 

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

1. 概念     Java DataBase Connectivity  Java 数据库连接， Java语言操作数据库     JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。     各个数据库厂商去实现这套接口，提供数据库驱动jar包。     我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。   2. 快速入门    步骤         1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar             1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下               (在模块下新建libs文件夹，与src同级)(建目录方便管理，建不建都行，名称任意)             2.在jar包/libs目录上右键-->Add As Library     2. 注册驱动     3. 获取数据库连接对象 Connection     4. 定义sql语句     5. 获取执行sql语句的对象 Statement     6. 执行sql，接收返回结果     7. 处理结果     8. 释放资源

?

1 2 3 4 5 6

CREATE TABLE account (             id INT PRIMARY KEY AUTO_INCREMENT,             NAME VARCHAR(10),             balance DOUBLE ); INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

package com.yub4by.jdbc;

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

public class JDBCDemo1 {
    public static void main(String[] args) throws Exception {
        //1 导入驱动jar包

        //2 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //3 获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hm_db2", "root", "root");
//        Connection conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");

        //4 定义sql语句
        String sql = "update account set balance=500 where id=1";

        //5 获取执行sql语句的对象
        Statement stmt = conn.createStatement();

        //6 执行sql语句，接收返回结果
        int count = stmt.executeUpdate(sql);

        //7 处理结果
        System.out.println(count); //1

        //8 释放资源
        stmt.close();
        conn.close();

    }
}

优化写法-例子

package com.yub4by.jdbc;

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

/**
 * 练习：
 *             1. account表 添加一条记录
 *             2. account表 修改记录
 *             3. account表 删除一条记录
 */
//添加一条记录
public class JDBCDemo2 {
    public static void main(String[] args) {


        Connection conn = null;
        Statement stmt = null;
        try {
            //1注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2获取数据库连接对象Connection
//            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hm_db2", "root", "root");
            conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");

            //3获取执行sql语句的对象Statement
            stmt = conn.createStatement();

            //4定义sql语句
            String sql = "insert into account values(null, '王五', 3000)";

            //5执行sql语句
            int count = stmt.executeUpdate(sql); //返回影响数据库的行数

            //6处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            //7释放资源
            if(stmt != null){ //避免空指针异常
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

package com.yub4by.jdbc;

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

/**
 * 练习：
 *             1. account表 添加一条记录
 *             2. account表 修改记录
 *             3. account表 删除一条记录
 */
//修改记录
public class JDBCDemo3 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");
            stmt = conn.createStatement();

            String sql = "update account set balance=1500 where id=3";
            int count = stmt.executeUpdate(sql);
            System.out.println(count);
            if(count != 0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

package com.yub4by.jdbc;

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

/**
 * 练习：
 *             1. account表 添加一条记录
 *             2. account表 修改记录
 *             3. account表 删除一条记录
 */
//删除一条记录
public class JDBCDemo4 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");
            stmt = conn.createStatement();

            String sql = "delete from account where id=3";
            int count = stmt.executeUpdate(sql);
            System.out.println(count);
            if(count != 0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

package com.yub4by.jdbc;

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

//创建表
public class JDBCDemo5 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");
            stmt = conn.createStatement();

            String sql = "create table student(id int, name varchar(20))";
            int count = stmt.executeUpdate(sql);
            System.out.println(count); //0
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}