sqlyog中写入代码新建数据库

/*在MySQL中创建一个名称为chapter01的数据库，
  然后在该数据库中创建一个users表，SQL语句如下所示*/
CREATE DATABASE jdbc;
USE jdbc;
CREATE TABLE users(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(40),
        password VARCHAR(40),
        email VARCHAR(60),
        birthday DATE 
)CHARACTER SET utf8 COLLATE utf8_general_ci;

/*数据库和表创建成功后，再向users表中插入3条数据，SQL语句如下所示*/
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('zs','123456','zs@sina.com','1980-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('lisi','123456','lisi@sina.com','1981-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('wangwu','123456','wangwu@sina.com','1979-12-04');
/*为了查看数据是否添加成功，使用SELECT语句查询users表，SQL语句如下所示*/

package Bean;

import java.util.Date;

public class User {
        private int id;
        private String name;
        private String password;
        private String email;
        private Date birthday;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
            return password;
        }
        public void setPassword(String password) {
            this.password = password;
        }
        public String getEmail() {
            return email;
        }
        public void setEmail(String email) {
            this.email = email;
        }
        public Date getBirthday() {
            return birthday;
        }
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
}

package Dao;

import Bean.User;
import utils.C3P0Utils;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    // 添加用户的操作
    public boolean insert(User user) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
//            conn = C3P0Utils.getConnection();

            // 发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(user.getBirthday());
            String sql = " INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES(?,?,?,?)";
            // 获得Statement对象
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getEmail());
            stmt.setString(4, birthday);
            int num = stmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }

    //查询所有，返回的是多条数据对应的bean对象
    public List<User> getall() {
        List<User> list = new ArrayList<>();//删除集合存储查询到的所有对象
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        try {
            connection = JDBCUtils.getConnection();//连接数据库
            String sql = "select * from users";//SQL语句
            statement = connection.prepareStatement(sql);//获得preparedStatment对象
            set = statement.executeQuery();//执行SQL语句
            //操作结果集
            while (set.next()) {
                //获得每条记录各个字段的值
                int id = set.getInt("id");
                String name = set.getString("name");
                String password = set.getString("password");
                String email = set.getString("email");
                String birthday = set.getString("birthday");
                //将各个字段的值封装进bean对象
                User user = new User();
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                user.setEmail(email);
                user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));//要把字符串birthday转换成Date
                //将user放入集合
                list.add(user);
            }
            return list;//将集合返回给调用者
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(set, statement, connection);
        }
        return null;//如果发生异常，返回null
    }

    //查询单个用户-----ID
    public User getByID(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        try {
            connection = JDBCUtils.getConnection();//连接数据库
            String sql = "select * from users where id="+id;//SQL语句
            statement = connection.prepareStatement(sql);//获得preparedStatment对象
            set = statement.executeQuery();//执行SQL语句
            while (set.next()) {
                //获取查询到的该条记录的各个字段的值
                int userid = set.getInt("id");
                String name = set.getString("name");
                String password = set.getString("password");
                String email = set.getString("email");
                String birthday = set.getString("birthday");
                //将各个字段的值封装进bean对象
                User user = new User();
                user.setId(userid);
                user.setName(name);
                user.setPassword(password);
                user.setEmail(email);
                user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));//要把字符串birthday转换成Date
                return user;//将用户返回给调用者
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(set, statement, connection);
        }
        return null;
    }
    //删除
    public boolean delet(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();

            // 书写SQL语句
            String sql="delete from users where id="+id;
            //获得preparedstatment对象
            stmt=conn.prepareStatement(sql);
            //执行sql语句
            int num = stmt.executeUpdate();
            if (num > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }

//修改
    public boolean update(User user){
        Connection connection = null;
        PreparedStatement statement = null;
        try{
            connection=JDBCUtils.getConnection();
            String sql="update users set name=?,password=?,email=?,birthday=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setString(2,user.getPassword());
            statement.setString(3,user.getEmail());
            statement.setString(4,new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()));
            statement.setInt(5,user.getId());

            int num = statement.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(statement,connection);
        }
        return false;
        }
    }

package utils;

import java.sql.*;

public class JDBCUtils{

    //获得数据库连接
public static Connection getConnection() throws Exception{
    Statement stmt=null;
    ResultSet rs=null;
    Connection conn=null;

            // 1. 注册数据库的驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.通过DriverManager获取数据库连接
            String url = "jdbc:mysql://localhost:3306/jdbc";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username,
                    password);
            return conn;
        }

        //释放资源
    public static void release(ResultSet rs,Statement statement,Connection conn){
        if(rs!=null){
            try{
                rs.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
            rs=null;
        }
        if(statement!=null){
            try{
                statement.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
            statement=null;
        }
        if(conn!=null){
            try{
                conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
            conn=null;
        }

        }
        public static void release(Statement statement,Connection connection){
            if(statement!=null){
                try{
                    statement.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
                statement=null;
            }
            if(connection!=null){
                try{
                    connection.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
                connection=null;
            }
        }
}