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;
        }
    }
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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);
    }

}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

查询相关信息

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));
    }
}

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185

用户类,实体类

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 + "]";
    }

}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

创建数据库语句

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