Axino.net

Since this is not trivial as one might think. I will go slowly trough the process of creating connection and finally statement (reading data, writing or deleting ).

First thing you need is access to the mysql server, I strongly suggest using WAMP or XAMPP for windows based users or LAMP or XAMPP for linux based users. After you install any of these packages you are required to start mysql database server (service). Of course if you want to create some sort of database you should either use command line interface for mysql or phpmyadmin which comes included in packages I suggested before. PhpMyAdmin is used for operating MySQL based database and great learning tool as it for any action you do trough interface you will be able to see SQL code.

Now let’s continue with Java.

To star connection you will require following code:

Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/somedb", "root", "");

The following statement means that you are using java database connection using mysql protocol connecting to somedb meanwhile user with which you connect to database is root and this user is using empty password as it is default in the previously mentioned packages.

When you execute previous statement you are establishing connection to database. On the other hand very important thing is to close connection to database otherwise you will slow down your server and your application will work slowly.

To close connection to database you need to invoke method in Connection object:”

conn.close();

Now let’s say that we have table in the database somedb named students. Table students contains fields student_id, name, surname, citizenship.  To read data from this table we are going to use basic SQL statement.

SELECT * FROM students

Upper statement means that we select all fields that is all columns and rows from table students. Now let’s see how to execute this statement.

PreparedStatement studs = conn.prepareStatement("SELECT * FROM students");

This means that we are preparing statement to be execute trough our established connection. Also if you have googled or binged then you might have noticed that some are using statement instead of preparedstatement. Difference is huge! Not in this particular example but as you get better it is always good to use proper programming from beginning then to start changing something later on when you figure that it is insecure or wrong. Yes the difference is in security just on short notice. In statement you can place trough SQL injection but if you use preparedstatement this will not pass checking or all special characters will be escaped.

Next step is to execute our query!

ResultSet rs = studs.executeQuery();

This represents execution of preparedstatement studs. Returned object is ResultSet from which we are able to obtain data from database.

Now let’s say that you would like read data about student in the first row. You should use following code.

rs.getInt("student_id");
rs.getString("name");
rs.getString("surname");
rs.getString("citizenship");

Using that code you can obtain all data that can be stored in this table. To move on the next record you will use following method:

rs.next();

And always remember to close the connection to the database!

Similar Posts:

• How to grant privilege to users on MySQL database
• Addind content in JTable
• Basic web hacking 8 :: simple SQL
• mysql server and ubuntu
• java glassfish and ubuntu server