MySQL Server and Java: How to connect and then make a table select?
MySQL is a flexible relational database manager that makes of your data a powerful source of integration with all kind of web programming tools. With MySQL you can make a connection from a program developed on Java. I prepared this example in Java because commonly most of developers using MySQL Server are developing applications in PHP and ASP.NET but I would like to show you that can be easily done in almost every kind of application. The following code is a demonstration for educative purposes and you are free to apply this example to your own project and modify it according to your style and your needs. For this example you must download the version 3.0 or higher of the mysql-connector for Java.
// // before run this script you should download the version 3.0 or higher of the mysql-connector-java // Here you have to insert your username and password: // String userName = "customizeyourusername"; // String password = "customizeyourpassord"; // // // Compile: // javac Select.java // (or you can also use GCC's gcj "gcj -C Select.java") // http://gcc.gnu.org/onlinedocs/gcj/ // http://www.linuxjournal.com/article.php?sid=4860 // http://gcc.gnu.org/java/papers/cni/t1.html // // Run: // java Select // // // The program above assumes "exams" has been created in // the "test" database. If you create "exams" change the // select statment to read: // SELECT pkey,name,exam,score FROM yourdatabase.exams // // // CREATE TABLE exams ( // pkey int(11) NOT NULL auto_increment, // name varchar(15), // exam int, // score int, // PRIMARY KEY (pkey) // // ); // // insert into exams (name,exam,score) values ('Bob',1,75); // insert into exams (name,exam,score) values ('Bob',2,77); // insert into exams (name,exam,score) values ('Bob',3,78); // insert into exams (name,exam,score) values ('Bob',4,80); // // insert into exams (name,exam,score) values ('Sue',1,90); // insert into exams (name,exam,score) values ('Sue',2,97); // insert into exams (name,exam,score) values ('Sue',3,98); // insert into exams (name,exam,score) values ('Sue',4,99); // import java.sql.*; public class Select { public static void main (String[ ] args) { Connection conn = null; String url = "jdbc:mysql://localhost/"; String userName = "username1"; String password = "password1"; try { Class.forName ("com.mysql.jdbc.Driver").newInstance ( ); conn = DriverManager.getConnection (url, userName, password); // System.out.println ("Connected"); Statement s = conn.createStatement ( ); s.executeQuery ("SELECT pkey,name,exam,score FROM test.exams"); ResultSet rs = s.getResultSet ( ); int count = 0; while (rs.next ( )) // loop through rows of result set { int pkey = rs.getInt (1); String name = rs.getString(2); int exam = rs.getInt(3); int score = rs.getInt(4); ++count; System.out.println (count + ",inum: " + pkey + ",name: " + name + ",exam: " + exam + ",score: " + score ); } rs.close ( ); // close result set s.close ( ); // close statement System.out.println (count + " rows were returned"); } catch (Exception e) { System.err.println ("Cannot connect to server"+e); } finally { if (conn != null) { try { conn.close ( ); // System.out.println ("Disconnected"); /* for debugging */ } catch (Exception e) { /* ignore close errors */ } } } } }
