JAVA学习笔记(五十一)- DBUtil 封装数据库工具类

数据库工具类

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/* * 数据库工具类 */public class DBUtil {    // 获取数据库连接    public static Connection getConnection() {        String driverClassName = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://localhost:3306/test";        String user = "root";        String password = "123456";        Connection conn = null;        try {            Class.forName(driverClassName);            conn = DriverManager.getConnection(url, user, password);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    // 关闭所有    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {        try {            if (rs != null)                rs.close();            if (stmt != null)                stmt.close();            if (conn != null) {                conn.close();            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            rs = null;            stmt = null;            conn = null;        }    }}

JDBC 数据库连接实例

登录

import java.awt.BorderLayout;import java.awt.EventQueue;import javax.swing.JFrame;import javax.swing.JPanel;import javax.swing.border.EmptyBorder;import javax.swing.JTextField;import javax.swing.JLabel;import java.awt.FlowLayout;import javax.swing.JButton;public class Login extends JFrame {    private JPanel contentPane;    private JTextField textField;    /**     * Launch the application.     */    public static void main(String[] args) {        EventQueue.invokeLater(new Runnable() {            public void run() {                try {                    Login frame = new Login();                    frame.setVisible(true);                } catch (Exception e) {                    e.printStackTrace();                }            }        });    }    /**     * Create the frame.     */    public Login() {        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);        setBounds(100, 100, 450, 300);        contentPane = new JPanel();        contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));        contentPane.setLayout(new BorderLayout(0, 0));        setContentPane(contentPane);        JPanel panel = new JPanel();        contentPane.add(panel, BorderLayout.CENTER);        panel.setLayout(new FlowLayout(FlowLayout.CENTER, 5, 5));        JLabel label = new JLabel("用户名");        panel.add(label);        textField = new JTextField();        panel.add(textField);        textField.setColumns(20);        JButton btnNewButton = new JButton("登陆");        panel.add(btnNewButton);    }}

查询相关信息

import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;public class Test04 {    Connection conn = null;    PreparedStatement pstmt = null;    ResultSet rs = null;    // 根据编号查询用户信息    public User getUserById(int id) {        User user = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(                    "jdbc:mysql://localhost:3306/test", "root", "123456");            /*             * stmt = conn.createStatement(); String sql =             * "select * from user where id=" + id;             */            String sql = "select * from user where id=?";            pstmt = conn.prepareStatement(sql);            pstmt.setInt(1, id);            rs = pstmt.executeQuery();            if (rs.next()) {                user = new User(rs.getInt("id"), rs.getString("name"),                        rs.getString("password"), rs.getInt("age"),                        rs.getDate("birthday"));            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                rs.close();                pstmt.close();                conn.close();            } catch (SQLException e) {                e.printStackTrace();            } finally {                rs = null;                pstmt = null;                conn = null;            }        }        return user;    }    // 检查用户登陆，即判断用户名或密码是否正确    public boolean checkLogin(User user) {        boolean flag = false;        /*String sql = "select *  from user where name='" + user.getName()                + "' and password='" + user.getPassword() + "'";*/        String sql="select * from user where name=? and password=?";        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(                    "jdbc:mysql://localhost:3306/test", "root", "123456");            pstmt=conn.prepareStatement(sql);            pstmt.setString(1, user.getName());            pstmt.setString(2, user.getPassword());            rs=pstmt.executeQuery();            if(rs.next()){                flag=true;            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }finally{            try {                rs.close();                pstmt.close();                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        return flag;    }    //查询所有用户信息    public List<User> getAllUser(){        List<User> users=new ArrayList<User>();        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(                    "jdbc:mysql://localhost:3306/test", "root", "123456");            String sql = "select * from user";            pstmt = conn.prepareStatement(sql);            rs = pstmt.executeQuery();            while(rs.next()) {                users.add(new User(rs.getInt("id"), rs.getString("name"),                        rs.getString("password"), rs.getInt("age"),                        rs.getDate("birthday")));            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                rs.close();                pstmt.close();                conn.close();            } catch (SQLException e) {                e.printStackTrace();            } finally {                rs = null;                pstmt = null;                conn = null;            }        }        return users;    }    //用户注册，即添加用户    public void addUser(User user){        String sql="insert into user values (null,?,?,?,?)";        //调用工具类，获取连接Connection        conn=DBUtil.getConnection();        try {            pstmt=conn.prepareStatement(sql);            pstmt.setString(1, user.getName());            pstmt.setString(2,user.getPassword());            pstmt.setInt(3, user.getAge());            pstmt.setDate(4, new Date(user.getBirthday().getTime()));//将java.util.Date转换为java.sql.Date            pstmt.executeUpdate();            System.out.println("添加用户成功！");        } catch (SQLException e) {            e.printStackTrace();        }finally{            DBUtil.closeAll(rs, pstmt, conn);        }    }    //根据编号删除用户信息    public void deleteUserById(int id){    }    //更新用户信息    public void updateUser(User user){    }    //根据编号范围和姓名模糊查询    public List<User> getUserByCondition(int sid,int eid,String name){        return null;    }    public static void main(String[] args) {        Test04 test = new Test04();        User user = test.getUserById(2);        System.out.println(user);        User user=new User("赵超","222222");        boolean flag=test.checkLogin(user);        System.out.println("是否登陆成功？"+flag);        List<User> users=test.getAllUser();        System.out.println(users);        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");        java.util.Date birthday=null;        try {            birthday = sdf.parse("2014-2-14");        } catch (ParseException e) {            e.printStackTrace();        }        test.addUser(new User("余书石", "123456", 18, birthday));    }}

用户类,实体类

import java.util.Date;/* * 用户类,实体类 */public class User {    private int id;    private String name;    private String password;    private int age;    private Date birthday;    public User(int id, String name, String password, int age, Date birthday) {        super();        this.id = id;        this.name = name;        this.password = password;        this.age = age;        this.birthday = birthday;    }    public User() {    }    public User(String name, String password, int age, Date birthday) {        super();        this.name = name;        this.password = password;        this.age = age;        this.birthday = birthday;    }    public User(String name, String password) {        super();        this.name = name;        this.password = password;    }    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 int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public String toString() {        return "User[id=" + id + ",name=" + name + ",age=" + age + ",birthday="                + birthday + "]";    }}

创建数据库语句

create table user(    id int primary key auto_increment,    name varchar(20) not null,    password varchar(20),    age int,    birthday date);