基本資料庫連線的方式在上一篇文章：[Java] 連線至sql資料庫
1. IPaddress：統一數值方便取用，建一個interface
1
2
3
4
5
6
| public interface IPaddress { final String ip = "IP地址"; final String sqldbaccount = "使用者名稱"; final String sqldbpass = "使用者密碼"; final String dbName = "資料庫名稱";} |
Register.java，要使用時先new一個Register object，再呼叫此object的insertTable即可
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
| import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Register implements IPaddress{ private Connection connection = null; private Statement statement = null; private ResultSet resultSet = null; private PreparedStatement pStatement = null; private String dropdbSQL = "DROP TABLE User "; private String name; private String passwd; private String nickName; //建構子，初始化要註冊的資料，並與資料庫取得連線 public Register(String name, String nickName, String passwd){ this.name = name; this.passwd = passwd; this.nickName = nickName; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://"+ip+"/"+dbName+"?useUnicode=true&characterEncoding=Big5", sqldbaccount,sqldbpass); } catch (ClassNotFoundException e) { //System.out.println(e); }catch (SQLException e) { //System.out.println(e); } } public int insertTable(){ int confirm = selectTable();//先去資料表查使用者名稱有無重複 int flag = 0; String insertdbSQL = "insert into User(id,name,nickName, passwd) " + "select ifNULL(max(id),0)+1,?,?,? FROM User"; try { if (confirm==0) {//沒有重複使用者 pStatement = connection.prepareStatement(insertdbSQL); pStatement.setString(1, name); pStatement.setString(2, nickName); pStatement.setString(3, passwd); pStatement.executeUpdate(); creatTable_friend(); creatTable_chat(); flag = 1; } } catch (SQLException e) { //System.out.println(e); }finally{ closedb(); } return flag;//成功註冊回傳1 } public int selectTable(){ String selectSQL = "select * from User "; int flag = 0; try { statement = connection.createStatement(); resultSet = statement.executeQuery(selectSQL); while (resultSet.next()) { if (resultSet.getString("name").equals(name)) flag = 1; } } catch (SQLException e) { //System.out.println("selectTable:"+e); }finally{ closedb(); } return flag;//有重複使用者回傳1 } public void closedb(){ try { if (resultSet!=null){ resultSet.close(); resultSet = null; } if (statement != null) { statement.close(); statement = null; } if (pStatement != null) { pStatement.close(); pStatement = null; } } catch (SQLException e) { //System.out.println(e); } } } |
Login.java ，要使用時先new 一個Login object ，再呼叫此object的selectTable method即可
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
| import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Login implements IPaddress{ private Connection connection = null; private Statement statement = null; private ResultSet resultSet = null; private PreparedStatement pStatement = null; private String name; private String passwd; public Login(String name,String passwd){ this.name = name; this.passwd = passwd; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://"+ip+"/"+dbName+"?useUnicode=true&characterEncoding=Big5", sqldbaccount,sqldbpass); } catch (ClassNotFoundException e) { //System.out.println(e); }catch (SQLException e) { //System.out.println(e); } } public int selectTable(){ String selectSQL = "select * from User "; int flag = 0; try { statement = connection.createStatement(); resultSet = statement.executeQuery(selectSQL); while (resultSet.next()) {//如果使用者名稱和密碼都正確 if (resultSet.getString("name").equals(name)&&resultSet.getString("passwd").equals(passwd)){ flag = 1; } } } catch (SQLException e) { //System.out.println("selectTable:"+e); }finally{ closedb(); } return flag; } public void closedb(){ try { if (resultSet!=null){ resultSet.close(); resultSet = null; } if (statement != null) { statement.close(); statement = null; } if (pStatement != null) { pStatement.close(); pStatement = null; } } catch (SQLException e) { //System.out.println(e); } } } |
