JDBC简单封装连接mysql数据库

本文章是利用maven项目pom文件加载相关jar包完成连接数据库

一、创建maven项目

    不细说省略

二、在pom添加所需要的jar的加载地址

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.41</version><!--$NO-MVN-MAN-VER$ -->
</dependency>

    版本以需要而定

三、创建JdbcUtil工具类

package com.demo.JdbcDemo.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSet Data;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
	
	//连接数据库基本参数
	private static final String DRIVER = \"com.mysql.jdbc.Driver\";
	private static final String URL = \"jdbc:mysql://localhost:3306/test\";
	private static final String NAME = \"root\";
	private static final String PASSWORD = \"mysql\";
	
	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet  rs;
	
	//static块加载驱动类,获取数据库连接对象Connection
	static {
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		} catch (Exception e) {
			 e.printStackTrace();
		}
	}
	
	/**
	 * 增、删、改
	 * @param sql
	 * @param paras
	 * @return
	 */
	public int dml(String sql,  ... paras) {
		try {
			ps = conn.prepareStatement(sql);
			if (paras != null && paras.length > 0) {
				int i = 1;
				for (  p : paras) {
					ps.set (i, p);
					i++;
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(conn, ps, null);
		}
		return 0;
	}
	
	/**
	 * 查询
	 * @param sql
	 * @param paras
	 * @return
	 */
	public List<Map<String, >> dql(String sql,  ... paras) {
		List<Map<String, >> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			if (paras != null && paras.length > 0) {
				int i = 1;
				for (  p : paras) {
					ps.set (i, p);
				}
			}
			rs = ps.executeQuery();
			ResultSet Data rsmd = rs.get Data();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String,  > map = new HashMap<String,  >();
				for (int j = 1; j <= columnCount; j++) {
					map.put(rsmd.getColumnName(j), rs.get (j));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	private void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				if (ps != null) {
					try {
						ps.close();
					} catch (Exception e2) {
						e2.printStackTrace();
					}finally {
						if (conn != null) {
							try {
								conn.close();
							} catch (Exception e3) {
								e3.printStackTrace();
							}
						}
					}
				}
			}
		}
	}
}

四，测试封装jdbc类

package com.demo.JdbcDemo.test;

import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.demo.JdbcDemo.utils.JdbcUtil;

public class JdbcTest {
	
	/**
	 * 单个查询
	 */
	@Test
	public void dqlOne() {
		String sql = \"select * from student where id = ? \";
		List<Map<String,  >> dql = new JdbcUtil().dql(sql, 1);
		System.out.println(dql.toString());
	}
	
	/**
	 * 查询全部
	 */
	@Test
	public void dqlALL() {
		String sql = \"select * from student\";
		List<Map<String,  >> dql = new JdbcUtil().dql(sql);
		System.out.println(dql.toString());
	}
	
	/**
	 * 插入
	 */
	@Test
	public void insert() {
		String sql = \"insert into student(`name`,`age`) values(?,?)\";
		int dml = new JdbcUtil().dml(sql, \"name\", 10);
		System.out.println(dml);
	}
	
	/**
	 * 更新
	 */
	@Test
	public void updete() {
		String sql = \"update student set name = ? where id = ?\";
		int dml = new JdbcUtil().dml(sql, \"haha\", 1);
		System.out.println(dml);
	}
	
	/**
	 * 删除
	 */
	@Test
	public void delect() {
		String sql = \"delete from student where id=?\";
		int dml = new JdbcUtil().dml(sql,7);
		System.out.println(dml);
	}
}