三、JDBC——Statement

Statement

statement语句是SQL语句的描述，使用它可以操作各种SQL语句，包括DDL（数据定义语句，如创建表）、DML（CRUD）和DCL等。

---

1、在src中的com包下创建Test2.java

package com;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class Test2 {
    public static void main(String []args){
//      CreateTbale();
//      Insert();
//      Update();
//      Delete();
    }

    static void Delete(){
        Connection connection=DBUtil.open();
        String sqlString="delete from User where id>0 &&id<9";
        try {
            Statement statement=(Statement)connection.createStatement();
            statement.executeUpdate(sqlString);

        } catch (Exception e) {
            // TODO: handle exception
        }finally{
            DBUtil.close(connection);
        }
    }


    //更新
    static void Update(){
        Connection conn=DBUtil.open();
        String sql="update User set name='big cxg' where id>3";
        try {
            Statement statement=(Statement) conn.createStatement();
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(conn);
        }   
    }

    //增加数据
    static void Insert(){
        Connection conn=DBUtil.open();
        String sql="insert into User(name,email)values('cxg','cxg@qq.com')";
                try {
            Statement statement=(Statement) conn.createStatement();
            statement.executeUpdate(sql);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(conn);
        }
    }

    //创建表
    static void CreateTbale(){
        Connection conn=DBUtil.open();
        String sql="create table User(id int primary key auto_increment,name varchar(20),email varchar(20))";
        try {
            Statement statement=(Statement) conn.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            DBUtil.close(conn);
        }
    }
}

2、DBUtil.java作为连接数据库

package com;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.Reader;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Connection;

public class DBUtil {
    //定义成不可修改的
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    //把值都按要求填入
    static {
    //获取配置文件的内容
        Properties p=new Properties();
        Reader reader;
        try {
            reader = new FileReader("src//config.properties");//配置文件路径
            p.load(reader);//加载驱动文件
        } catch (Exception e) {
            e.printStackTrace();
        }

        driver=p.getProperty("driver");
        url=p.getProperty("url");
        username=p.getProperty("username");
        password=p.getProperty("password");
    }
    //打开数据库
    public static Connection open() {
        try {
            //注册加载驱动
            Class.forName(driver);
            //链接数据库
            return (Connection) DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //关闭数据库
    public static void close(Connection conn) {
        if(conn!=null)
        {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}