Java MySQL connection

This section, we introduce how to use Java to use JDBC to connect MySQL database.

Java needs to connect to the MySQL driver package Download the latest version of: http://dev.mysql.com/downloads/connector/j/ , obtained after extracting jar library file, and then import the library file in the corresponding project.

Jar package you can download the site provides: MySQL-Connector-Java-5.1.39-bin.jar

This example uses the Eclipse, import the jar package:

Create test data

Next we create w3big in MySQL database, create websites and data table, the table structure is as follows:

CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Insert some data:

INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '本教程', 'http://www.w3big.com', '5892', ''), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

Data table shown below:

Connect to the database

The following example uses JDBC to connect MySQL database, note that some of the data such as user names, passwords need to configure your development environment:

package com.w3big.test;

import java.sql.*;

public class MySQLDemo {

	// JDBC 驱动名及数据库 URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	static final String DB_URL = "jdbc:mysql://localhost:3306/w3big";

	// 数据库的用户名与密码，需要根据自己的设置
	static final String USER = "root";
	static final String PASS = "123456";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try{
			// 注册 JDBC 驱动
			Class.forName("com.mysql.jdbc.Driver");
		
			// 打开链接
			System.out.println("连接数据库...");
			conn = DriverManager.getConnection(DB_URL,USER,PASS);
		
			// 执行查询
			System.out.println(" 实例化Statement对...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT id, name, url FROM websites";
			ResultSet rs = stmt.executeQuery(sql);
		
			// 展开结果集数据库
			while(rs.next()){
				// 通过字段检索
				int id  = rs.getInt("id");
				String name = rs.getString("name");
				String url = rs.getString("url");
	
				// 输出数据
				System.out.print("ID: " + id);
				System.out.print(", 站点名称: " + name);
				System.out.print(", 站点 URL: " + url);
				System.out.print("\n");
			}
			// 完成后关闭
			rs.close();
			stmt.close();
			conn.close();
		}catch(SQLException se){
			// 处理 JDBC 错误
			se.printStackTrace();
		}catch(Exception e){
			// 处理 Class.forName 错误
			e.printStackTrace();
		}finally{
			// 关闭资源
			try{
				if(stmt!=null) stmt.close();
			}catch(SQLException se2){
			}// 什么都不做
			try{
				if(conn!=null) conn.close();
			}catch(SQLException se){
				se.printStackTrace();
			}
		}
		System.out.println("Goodbye!");
	}
}

Examples of the implementation of the above output results are as follows: