Techie Talk: Java MySql Connection!!

Well this semeser we have a course called Software Engeneering( pretty obvious for comp. sc students :-D ). But what's amazing is we have to learn the whole of Core Java, yes my frnzz WHOLE of it in 3 weeks. Including MySql, JDBC, Swings. Dont worry i just know the names!!

For all those who are stuck up with the Java Sql Connection problem here's a post u might be helped with:

First the steps which actually make the connnection:

1. Install a MySql server on the computer. It will run on 127.0.0.1 port:3306.
2. You need the Java Class corresponding to MySql for connection via JDBC( Java Data Base Connection ).
3. Make a Data Base with MySql.
4. Connect to the server with the code.
5. And Access the Data Base with the username password.

I will let u in with a example so hang on!

First of all the OS i am talkin is Debian( Ubuntu as an example ).

So the following are the steps!

1. Considering you have APT packaging toolkit installed, or Synaptic. Issue this command on the command line:

sudo apt-get install mysql-common

I take that it might be already installed.

2. Next install the server,

sudo apt-get install mysql-server

Dont try fancy options. Until you need a specific version. The above will install the most stable and recent version automatically!!

3. The server shall be starting automatically. So do this to check if it really is:

ps -e | grep mysqld

this will give u an o/p like:

sanket@sanket-laptop:~$ ps -e | grep mysql
5480 ? 00:00:00 mysqld_safe
5522 ? 00:00:00 mysqld

Thats what my OS shows. A mysqld is enough for now.

4. Confirm the port on which the server is running. Issue

sudo nmap -sS -sV -O -PI -PT 127.0.0.1 2> /dev/null | grep mysql

and u shall obtain something like this:

3306/tcp open mysql MySQL 5.0.51a-3ubuntu5.4

Which tells u that mysql running on 3306. Rest info can be helpful but not now.

5. Now we shall create a mysql database. We shall be using the root privellages only. They may be changed whenever required.

6. So start the mysql app by: mysql -p -u root
Enter the password.

7. For fun view the Databases already present, issue: show databases

My o/p:

mysql> show databases;
+--------------------+
| Database
+--------------------+
| information_schema
| mysql
| sanket
+--------------------+
3 rows in set (0.01 sec)

8. But we will be making a brand new database, so do this:

mysql> create database jdbc_test; ( mysql> is the prompt dont input it!! )

9. Now let's see if it's there:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jdbc_test |
| mysql |
| sanket |
+--------------------+
4 rows in set (0.00 sec)

I think u r pretty familiar with how i am entering the commands.
10. Change the working database:

mysql> use jdbc_test
Database changed

10. Now lets create a Table:

mysql> show tables;
Empty set (0.00 sec)

mysql> create table colm1 ( name VARCHAR(100));
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_jdbc_test |
+---------------------+
| colm1 |
+---------------------+
1 row in set (0.00 sec)

11. Enter some names.

mysql> insert into colm1 values ("sanket") , ("huha"), ("is.the.best"), ("windows_sux");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

12. Let's see what we have done:

mysql> select * from colm1;
+-------------+
| name |
+-------------+
| sanket |
| huha |
| is.the.best |
| windows_sux |
+-------------+
4 rows in set (0.00 sec)

So we're bang on.

13. Now let's get the connector: download it from
http://dev.mysql.com/downloads/connector/j/5.1.html

There shall be a Jar file in the archive named: mysql-connector-java-5.1.7-bin.jar

First extract and then unjar it.
On the command line u could do: java -jar

Newayz get hold of it, it will do with the GUI too :-/

Now where you xtracted the contents paste this to a Java file above the com folder that came with Jar file.

import java.sql.*;

public class AllTableName{
public static void main(String[] args) {
System.out.println("Listing all table name in Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbc_test";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = ""; //your password here
Statement stmt = null;
ResultSet rs = null;
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE"};
rs = dbm.getTables(null,null,"%",types);
System.out.println("Table name:");
while (rs.next()){
String table = rs.getString("TABLE_NAME");
System.out.println(table);
con.close();
}
}
catch (SQLException s){
System.out.println("No any table in the database");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

Put the root password in place!!

14. Now first compile the program using:

javac AllTableName.java
java AllTableName

Now if u see the following o/p u r done:

Listing all table name in Database!
Table name:
colm1

Voila ne doubts or Exceptions thrown please post in the comments i'll see to them!!