JDBC连接数据库模板

JDBC连接mysql数据库模板（eclipse）：

首先建立了一个叫test的数据库，和user数据表如图

CREATE TABLE user(
	uid INT AUTO_INCREMENT KEY,
	username VARCHAR(20),
     `password` VARCHAR(20)
);

INSERT user VALUES(NULL,'aaa','111','lmh');
INSERT user VALUES(NULL,'aaa','111','fsw');
INSERT user VALUES(NULL,'aaa','111','cx');

然后在eclipse中new一个dynamic web project

在src目录下new一个JDBC的带main的类。

把mysql的驱动jar包放到lib目录下即可，或者放在其它目录下然后右键build path.

连接数据库的步骤如图：

/*
	 * JDBc入门
	 */
	public static void main(String[] args) throws ClassNotFoundException {
		String password = "wow89757";
		
		Connection conn = null;
		java.sql.Statement stmt = null;
		ResultSet rs = null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");		
			//获得链接
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root",password);
			//创建执行SQL语句的对象，执行SQL
			String 	sql = "select * from user";					//查询语句
			stmt = conn.createStatement();			
			rs = stmt.executeQuery(sql);								//执行语句
			while(rs.next()) {
				int uid = rs.getInt("uid");
				String username = rs.getString("username");
				password = rs.getString("password");
				String name = rs.getString("name");
				
				System.out.println("uid:" + uid + "  username: " + username + "  password: " + password + "  name: " + name);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//释放资源
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// 
				}
				rs = null;
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// 
				}
				stmt = null;
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// 
				}
				conn = null;
			}
			
		}
		
	}

(注意：以上的drivermanager.registerDriver会导致加载2次驱动，

           connection资源有限很宝贵，如果不释放资源后期可能导致宕机，

           释放资源要放在finally代码块中，避免因为异常导致无法释放资源，

           connection使用原则 : 晚使用，早释放，

          由于statement在SQL注入有漏洞，所以实际开发会使用preparedStatement。

)

 

连接好后执行得到相应结果说明连接成功.

由于每次写数据库连接都需要些这套模板代码，所以可以抽取出来封装成utils的工具类

 

package com.imooc.jdbc.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.eclipse.jdt.internal.compiler.ast.NumberLiteral;

/*
 * JDBC 工具类
 */
public class JDBCUtils {
	private static final String driverClass;
	private static final String url;
	private static final String username;
	private static final String password;
	static {
		driverClass = "com.mysql.jdbc.Driver";
		url = "jdbc:mysql://127.0.0.1:3306/test";
		username = "root";
		password = "123456";
	}
	/*
	 * 	注册驱动
	 */	
	public static void loadDriver() throws ClassNotFoundException { 
		Class.forName(driverClass);
	}
	
	/*
	 * 获得连接
	 */
	public static Connection getConnection() throws Exception {
		loadDriver();
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;		
	}
	
	/*
	 * 资源释放
	 */
	public static void release(Statement stmt, Connection conn) {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = null;
		}
	}
	/*
	 * 释放资源
	 */
	public static void release(ResultSet rs, Statement stmt, Connection conn) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}
		
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = null;
		}
	}
	
//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//
//	}

}