jdbc mysql crud

 

 

1. 创建表

CREATE TABLE `t_user` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`password`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=6
ROW_FORMAT=COMPACT
;

 

2. jdbc 增删改查

package com.ryan.util;

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


public class DBUtil {
    private static final String DRIVERNAME = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/yde?useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    public static void main(String[] args) {
        delete();
        //update();
        //insert();
        //list();
    }
    //删除数据
    public static void delete() {
        Connection conn = getConn();
        String sql = "delete from t_user where id=?";
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setInt(1, 5);
            pstm.executeUpdate();
            System.out.println("删除成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }
    //修改数据
    public static void update() {
        Connection conn = getConn();
        String sql = "update t_user set password=? where id=?";
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setInt(2, 5);
            pstm.setString(1, "123456");
            pstm.executeUpdate();
            System.out.println("修改成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }
    //插入数据
    public static void insert() {
        Connection conn = getConn();
        String sql = "insert into t_user(username, password) values(?,?)";
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, "lisi");
            pstm.setString(2, "111111");
            pstm.executeUpdate();
            System.out.println("插入成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }
    //查询数据
    public static void list() {
        Connection conn = getConn();
        String sql = "select * from t_user";
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            //执行sql运行并返回结果集
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(2) + "," + rs.getString("password"));
            }
            System.out.println("查询成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }
    //获取连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(DRIVERNAME);//1. 加载驱动
            conn = DriverManager.getConnection(URL, USER, PASSWORD);//2. 获取数据库连接
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    //关闭连接
    public static void close(Connection conn) {
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
    
}

 

说明：导入mysql 驱动mysql-connector-java-5.1.6-bin.jar