Java JDBC

Java JDBC by mySQL,mariaDB

General Steps to Creating, Compiling and Running Java Programs
Applets  in Unix & Linux server

1. Open a Unix window.

2. Now, install  mariaDB or mySQL and install connectot/J
   For example: 
   

   prompt% sudo su .... prompt% apt-get install .....

3. Setting class path

   The JDBC connector will be located in the path /usr/share/java/. We can export the classpath as,

   export CLASSPATH=/usr/share/java/mysql-connector-java.jar:$CLASSPATH
   

   Grant privileges to user

   For the user connecting to the database, the privileges should be granted. This can be achieved using the below mysql query.

   GRANT ALL PRIVILEGES ON *.* TO ‘user’@’%’ IDENTIFIED BY ‘<user-password>’ WITH GRANT OPTION;
   

   Now, we can establish a connection to this machine's mysql server using the java program but only as a localhost. If we need to use this server's name or connect from some other machine, we will get several exceptions as discussed here and here.

   Edit /etc/hosts file

   It is to be noted that in the above step if I just used the IP address of the machine rather than the hostname of the machine, it worked. So, I suspected the issue was something related to dns name resolution.

   I modified my /etc/hosts file to have the below entry.

   127.0.0.1   servername.edu        localhost
   

   I was still not able to connect to the database using the hostname which I thought was pretty weird given that I have changed the /etc/hosts file as well. I even stopped ip6tables to check if that is the issue. However, none of the options worked and it was giving me the exception as,

   ERROR: java.net.UnknownHostException:connection refused
   

   Edit /etc/my.cnf file

   The last exception was promising. It says connection refused rather than saying something like not able to resolve the hostname. So, I thought something should be changed in the /etc/my.cnf file and I added the below lines to the file.

   port=3306
   bind-address=0.0.0.0
   

   When I made the above changes, I could connect to this machine from anywhere using the hostname

4. Using the editor of your choice, create some Java source code. 
   For example, suppose we want a class named "JDBCTest", 
       then we would start vi, emacs, pico, or whatever editor with the name of the Java file: 
   

   prompt% vi JDBCTest.java

   or

   prompt% emacs JDBCTest.java

   or

   prompt% pico JDBCTest.java

5. The source code, JDBCTest.java, might look like this: 
   

   package db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBManager { String driver = "org.mariadb.jdbc.Driver"; String url = "jdbc:mariadb://localhost:3306/test_db"; String uId = "jack"; String uPwd = "jack"; Connection con; PreparedStatement pstmt; ResultSet rs; public DBManager() { try { Class.forName(driver); con = DriverManager.getConnection(url, uId, uPwd); if( con != null ){ System.out.println("데이터 베이스 접속 성공"); } } catch (ClassNotFoundException e) { System.out.println("드라이버 로드 실패"); } catch (SQLException e) { System.out.println("데이터 베이스 접속 실패"); } } public void select(){ String sql = "select * from board"; try { pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ System.out.println("idx : " + rs.getInt("idx")); System.out.println("writer : " + rs.getString("writer")); System.out.println("title : " + rs.getString("title")); System.out.println("content : " + rs.getString("content")); } } catch (SQLException e) { System.out.println("쿼리 수행 실패"); } } public static void main(String[] args){ DBManager dbm = new DBManager(); dbm.select(); } }

   Don't forget to save the file!

6. Double check the name of the Java program file you just saved 
       by listing out the files in the current directory: 
   

   prompt% ls

8. Assuming we saved the Java source code (as in step 4) from within your editor, 
       we can now go back to the command prompt and compile it. 
   This is done with the javac compiler command: 
   

   prompt% javac JDBCTest.java