Connecting a Java application with a MySQL database

(En Español / In Spanish: https://josemmsimo.wordpress.com/2012/08/01/conectando-una-aplicacion-java-con-una-base-de-datos-mysql/)

For a programmer is minimally necessary be capable to use databases. This is, in my case, one of the aspects most “beautiful” of programming, because that is where you manage the most important: The information. The way of representing it is infinite, and how to access and manage this information is multiple. I will not explain too many details, but in large databases (with millions of records), it is extremely important that the database is properly designed.

To carry out this practice I draw in my system present:

  • WAMP (Windows + Apache + MySQL + PHP), although it would be XAMP if you were in GNU/Linux environment or other one in another system … Finally, MySQL is what interests us.
  • Eclipse to write Java program.
  • JDK and other basic elements of Java.

First, I specify my database. Your name will be “agenda” and will contain a single table that it is “personal”. The table will have: ID (“identificador”), name (“nombre”), phone (“telefono”) and e-mail (easy and intuitive). Before creating this table, it checks if it exist, in order to eliminate and prevent little problems. In the tests are also created a total of 4 registers, and then are displayed on screen.

Focusing on the part of Java, to make use of the database will have to import the package “java.sql.”

If we have never made ​​a connection from Java to the database, you may not have the driver installed. This driver allows the connection, and can be downloaded from here: We http://dev.mysql.com/downloads/connector/j/

Options for use:

  • Put in $ JAVA_HOME/lib/ and so will be accessible forever by default.
  • Put in the CLASSPATH.
  • Adding to our project we use the IDE (eg eclipse).

I will not explain this because there are many pages that show how, even if you have any questions please consult here and I will answer.

With respect to the source code is fairly intuitive. The long version, in which the steps are divided into small methods for further testing, it can be rather heavy. If you prefer something more “light”, you have a second version “lighter”.

Heavy version:

import java.sql.*;

public class conectarMostrar {
	private static Connection conexion = null;
	private static String bd = "agenda"; // Nombre de BD.
	private static String user = "root"; // Usuario de BD.
	private static String password = ""; // Password de BD.
	// Driver para MySQL en este caso.
	private static String driver = "com.mysql.jdbc.Driver";
	// Ruta del servidor.
	private static String server = "jdbc:mysql://localhost/" + bd;

	public static void main(String[] args) throws SQLException {

		System.out.println("INICIO DE EJECUCIÓN.");
		conectar();
		Statement st = conexion();

		// Se elimina la tabla "personal" en caso de existir.
		String cadena = "DROP TABLE IF EXISTS personal;";
		consultaActualiza(st, cadena);

		// Se crea la tabla "personal"
		cadena = "CREATE TABLE personal (`Identificador` int(11) NOT NULL AUTO_INCREMENT, `Nombre` varchar(50) NOT NULL, `Apellidos` varchar(50) NOT NULL, `Telefono` varchar(9) DEFAULT NULL, `Email` varchar(60) DEFAULT NULL, PRIMARY KEY (`Identificador`))";
		consultaActualiza(st, cadena);

		// Se crean datos de prueba para utilizarlos en la tabla "personal"
		cadena = "INSERT INTO personal (`Identificador`, `Nombre`, `Apellidos`, `Telefono`, `Email`) VALUES (1, 'José', 'Martínez López', '968112233', 'jose@martinezlopez.com'), (2, 'María', 'Gómez Muñoz', '911876876', 'maria@gomezoliver.com'), (3, 'Juan', 'Sánchez Fernández', '922111333', 'juan@sanchezfernandez.com'), (4, 'Ana', 'Murcia Rodríguez', '950999888', 'ana@murciarodriguez.com');";
		consultaActualiza(st, cadena);

		// Se sacan los datos de la tabla personal
		cadena = "SELECT * FROM personal;";
		ResultSet rs = consultaQuery(st, cadena);
		if (rs != null) {
			System.out.println("El listado de persona es el siguiente:");

			while (rs.next()) {
				System.out.println("  ID: " + rs.getObject("Identificador"));
				System.out.println("  Nombre completo: "
						+ rs.getObject("Nombre") + " "
						+ rs.getObject("Apellidos"));

				System.out.println("  Contacto: " + rs.getObject("Telefono")
						+ " " + rs.getObject("Email"));

				System.out.println("- ");
			}
			cerrar(rs);
		}
		cerrar(st);
		System.out.println("FIN DE EJECUCIÓN.");
	}

	/**
	 * Método neecesario para conectarse al Driver y poder usar MySQL.
	 */
	public static void conectar() {
		try {
			Class.forName(driver);
			conexion = DriverManager.getConnection(server, user, password);
		} catch (Exception e) {
			System.out.println("Error: Imposible realizar la conexion a BD.");
			e.printStackTrace();
		}
	}

	/**
	 * Método para establecer la conexión con la base de datos.
	 *
	 * @return
	 */
	private static Statement conexion() {
		Statement st = null;
		try {
			st = conexion.createStatement();
		} catch (SQLException e) {
			System.out.println("Error: Conexión incorrecta.");
			e.printStackTrace();
		}
		return st;
	}

	/**
	 * Método para realizar consultas del tipo: SELECT * FROM tabla WHERE..."
	 *
	 * @param st
	 * @param cadena La consulta en concreto
	 * @return
	 */
	private static ResultSet consultaQuery(Statement st, String cadena) {
		ResultSet rs = null;
		try {
			rs = st.executeQuery(cadena);
		} catch (SQLException e) {
			System.out.println("Error con: " + cadena);
			System.out.println("SQLException: " + e.getMessage());
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * Método para realizar consultas de actualización, creación o eliminación.
	 *
	 * @param st
	 * @param cadena La consulta en concreto
	 * @return
	 */
	private static int consultaActualiza(Statement st, String cadena) {
		int rs = -1;
		try {
			rs = st.executeUpdate(cadena);
		} catch (SQLException e) {
			System.out.println("Error con: " + cadena);
			System.out.println("SQLException: " + e.getMessage());
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * Método para cerrar la consula
	 *
	 * @param rs
	 */
	private static void cerrar(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.print("Error: No es posible cerrar la consulta.");
			}
		}
	}

	/**
	 * Método para cerrar la conexión.
	 *
	 * @param st
	 */
	private static void cerrar(java.sql.Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {
				System.out.print("Error: No es posible cerrar la conexión.");
			}
		}
	}
}

 

Light version:


import java.sql.*;

public class conectarMostrar2 {
	public static void main(String[] args) throws SQLException {
		System.out.println("INICIO DE EJECUCIÓN.");
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost/agenda", "root", "");
			Statement st = conexion.createStatement();
			st.executeUpdate("DROP TABLE IF EXISTS personal;");
			st.executeUpdate("CREATE TABLE personal (`Identificador` int(11) NOT NULL AUTO_INCREMENT, `Nombre` varchar(50) NOT NULL, `Apellidos` varchar(50) NOT NULL, `Telefono` varchar(9) DEFAULT NULL, `Email` varchar(60) DEFAULT NULL, PRIMARY KEY (`Identificador`));");
			st.executeUpdate("INSERT INTO personal (`Identificador`, `Nombre`, `Apellidos`, `Telefono`, `Email`) VALUES (1, 'José', 'Martínez López', '968112233', 'jose@martinezlopez.com'), (2, 'María', 'Gómez Muñoz', '911876876', 'maria@gomezoliver.com'), (3, 'Juan', 'Sánchez Fernández', '922111333', 'juan@sanchezfernandez.com'), (4, 'Ana', 'Murcia Rodríguez', '950999888', 'ana@murciarodriguez.com');");
			ResultSet rs = st.executeQuery("SELECT * FROM personal;");

			if (rs != null) {
				System.out.println("El listado de persona es el siguiente:");

				while (rs.next()) {
					System.out.println("  ID: " + rs.getObject("Identificador"));
					System.out.println("  Nombre completo: " + rs.getObject("Nombre") + " "	+ rs.getObject("Apellidos"));
					System.out.println("  Contacto: " + rs.getObject("Telefono") + " " + rs.getObject("Email"));
					System.out.println("- ");
				}
				rs.close();
			}
			st.close();

		}
		catch(Exception s)
		{
			System.out.println("Error: Varios.");
			System.out.println("SQLException: " + s.getMessage());
		}
		System.out.println("FIN DE EJECUCIÓN.");
	}
}

If you do not get it to work, the possible reasons may be diverse, usually forgetfulness.
Tip: Try to follow the steps, looking in Internet solutions, and as a last resort consulting here :-)

Leave a comment

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s