Javadb Study Notes

Derby is not a new database product. It is a pure Java Database donated by IBM to the Apache dB project. The Derby version included in jdk6.0 is 10.2.1.7, supports stored procedures and triggers. There are two operating modes: embedded database and network database, the former database server and client are both running in the same JVM, and the latter allows the database server and client to be not in the same JVM, and the two are allowed on different physical machines. it is worth noting that the Derby in JDK 6 supports the jdk6 new JDBC 4.0 specification (JSR 221). Now, if we want to practice JDBC usage, there is no need to install a database product separately, use Derby directly.
Here is a simple example of using Derby:
First, import the jar package: Derby. Jar. If you have installed JDK 6, you can find it in the C:/program files/Sun/javadb/lib directory.
Create a database:

Java code

1. Private connection getconnection () throws sqlexception {
2. Connection connection = drivermanager
3. . Getconnection ("JDBC: Derby: userdb; Create = true; user = test; Password = test ");
4. Connection. setautocommit (false );
5. Return connection;
6. }

private Connection getConnection() throws SQLException {Connection connection = DriverManager.getConnection("jdbc:derby:userDB;create=true;user=test;password=test");connection.setAutoCommit(false);return connection;}

Userdb is the name of the database to be connected, and create = true indicates that the database is created if the database does not exist. If the database exists, the user = test is used; password = test to connect to the database.

With the database, we should create the table:

Java code

1. Private void createtable (connection) throws sqlexception {
2. Statement statement = connection. createstatement ();
4. String SQL = "CREATE TABLE users ("
5. + "Id bigint not null generated by default as identity ,"
6. + "User_name varchar (20) not null ,"
7. + "Password varchar (20 ),"
8. + "Constraint p_key_1 primary key (ID ))";
9. Statement.exe cute (SQL );
11. SQL = "create unique index user_name_index on users ("
12. + "User_name ASC )";
13. Statement.exe cute (SQL );
15. Statement. Close ();
16. }

private void createTable(Connection connection) throws SQLException {Statement statement = connection.createStatement();String sql = "create table USERS("+ "   ID                           BIGINT                       not null generated by default as identity,"+ "   USER_NAME            VARCHAR(20)            not null,"+ "   PASSWORD             VARCHAR(20),"+ "   constraint P_KEY_1 primary key (ID))";statement.execute(sql);sql = "create unique index USER_NAME_INDEX on USERS ("+ "   USER_NAME            ASC)";statement.execute(sql);statement.close();}

The users table has been created, which includes three columns: ID, user_name, and password. ID is the primary key. The generated by default as identity function is similar to sequence, and identity is the column automatically added,
Generated by always as identity
Generated by default as identity
By always and by default indicate how to generate this identity.
By always is automatically generated by the system.
By default is a value that can be specified by the user.
Compile the JavaBean corresponding to the users table (this is not to be said ),:

Java code

1. Public class user implements serializable {
3. /**
4. *
5. */
6. Private Static final long serialversionuid = 1l;
8. Private long ID;
10. Private string username;
12. Private string password;
14. Public long GETID (){
15. Return ID;
16. }
18. Public void setid (long ID ){
19. This. ID = ID;
20. }
22. Public String GetUserName (){
23. Return username;
24. }
26. Public void setusername (string username ){
27. This. Username = username;
28. }
30. Public String GetPassword (){
31. Return password;
32. }
34. Public void setpassword (string password ){
35. This. Password = password;
36. }
37. }

public class User implements Serializable {/** *  */private static final long serialVersionUID = 1L;private Long id;private String userName;private String password;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}}

Next, you can add, delete, modify, and query the database:

Insert data:

Java code

1. Private void create (User user ){
2. Connection connection = NULL;
4. Try {
5. Connection = This. getconnection ();
6. Preparedstatement Statement = connection
7. . Preparestatement ("insert into users (user_name, password) values (?,?) ");
9. Int Index = 1;
10. Statement. setstring (index ++, user. GetUserName ());
11. Statement. setstring (index ++, user. GetPassword ());
13. Statement.exe cute ();
15. User. setid (this. GETID (connection ));
17. Connection. Commit ();
18. } Catch (sqlexception e ){
19. Rollback (connection );
20. Throw new runtimeexception (E );
21. } Finally {
22. If (connection! = NULL ){
23. Close (connection );
24. }
25. }
26. }

private void create(User user) {Connection connection = null;try {connection = this.getConnection();PreparedStatement statement = connection.prepareStatement("insert into users (user_name,password) values(?,?)");int index = 1;statement.setString(index++, user.getUserName());statement.setString(index++, user.getPassword());statement.execute();user.setId(this.getId(connection));connection.commit();} catch (SQLException e) {rollback(connection);throw new RuntimeException(e);} finally {if (connection != null) {close(connection);}}}

 

Java code

1. Private long GETID (connection) throws sqlexception {
2. Callablestatement = connection
3. . Preparecall ("values identity_val_local ()");
5. Resultset = callablestatement.exe cutequery ();
6. Resultset. Next ();
7. Long id = resultset. getlong (1 );
8. Resultset. Close ();
9. Callablestatement. Close ();
10. Return ID;
11. }

private Long getId(Connection connection) throws SQLException {CallableStatement callableStatement = connection.prepareCall("values identity_val_local()");ResultSet resultSet = callableStatement.executeQuery();resultSet.next();Long id = resultSet.getLong(1);resultSet.close();callableStatement.close();return id;}

The GETID method obtains the default system id value through identity_val_local (), while the identity_val_local () function can be executed after the insert statement is executed, returns the value generated by the System ID. I still think about curr_val of sequence.

Modify data:

Java code

2. Private void Update (User user ){
3. Connection connection = NULL;
5. Try {
6. Connection = This. getconnection ();
7. Preparedstatement Statement = connection
8. . Preparestatement ("Update users set user_name = ?, Password =? Where id =? ");
10. Int Index = 1;
11. Statement. setstring (index ++, user. GetUserName ());
12. Statement. setstring (index ++, user. GetPassword ());
13. Statement. setlong (index ++, user. GETID ());
15. Statement.exe cute ();
17. Connection. Commit ();
18. } Catch (sqlexception e ){
19. Rollback (connection );
20. Throw new runtimeexception (E );
21. } Finally {
22. If (connection! = NULL ){
23. Close (connection );
24. }
25. }
26. }

private void update(User user) {Connection connection = null;try {connection = this.getConnection();PreparedStatement statement = connection.prepareStatement("update users set user_name=?,password=? where id=?");int index = 1;statement.setString(index++, user.getUserName());statement.setString(index++, user.getPassword());statement.setLong(index++, user.getId());statement.execute();connection.commit();} catch (SQLException e) {rollback(connection);throw new RuntimeException(e);} finally {if (connection != null) {close(connection);}}}

Delete data:

Java code

2. Public void Delete (long ID ){
3. Connection connection = NULL;
5. Try {
6. Connection = This. getconnection ();
7. Preparedstatement Statement = connection
8. . Preparestatement ("delete from users where id =? ");
9. Statement. setlong (1, ID );
10. Statement.exe cute ();
11. Connection. Commit ();
12. } Catch (sqlexception e ){
13. Rollback (connection );
14. Throw new runtimeexception (E );
15. } Finally {
16. If (connection! = NULL ){
17. Close (connection );
18. }
19. }
20. }

public void delete(Long id) {Connection connection = null;try {connection = this.getConnection();PreparedStatement statement = connection.prepareStatement("delete from users where id=?");statement.setLong(1, id);statement.execute();connection.commit();} catch (SQLException e) {rollback(connection);throw new RuntimeException(e);} finally {if (connection != null) {close(connection);}}}

Query data:

Java code

1. Public user findbyid (long ID ){
2. Connection connection = NULL;
4. Try {
5. Connection = This. getconnection ();
7. Preparedstatement Statement = connection
8. . Preparestatement ("select user_name, password from users where id =? ");
9. Statement. setlong (1, ID );
10. Resultset = statement.exe cutequery ();
12. User user = NULL;
14. If (resultset. Next ()){
15. User = new user ();
16. User. setid (ID );
17. User. setusername (resultset. getstring ("user_name "));
18. User. setpassword (resultset. getstring ("password "));
19. }
21. Resultset. Close ();
22. Statement. Close ();
23. Connection. Commit ();
24. Return user;
25. } Catch (sqlexception e ){
26. Throw new runtimeexception (E );
27. } Finally {
28. If (connection! = NULL ){
29. Close (connection );
30. }
31. }
32. }