使用JDBC操作数据库

使用JDBC操作数据库

简单的对数据库进行增删改查操作
开发环境
    JDK1.8.0_151
    Eclipse-jee-neon-3-win32-x86_64

DBHelper.java

package mysql.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/***
 * @ProjectName:  JDBC
 * @PackageName:  mysql.jdbc 
 * @ClassName: DBHelper 
 * @Description: 封装DBHelper类，对数据库实现     增删改查
 * @Author： 伏永正
 * @Date： 2017年10月31日 下午1:43:14
 */
public class DBHelper {
	private static String url="jdbc:mysql://localhost:3306/smxpt";	//数据库地址jdbc:mysql://连接主机ip:端口号/数据库名
	private static String username="root";	//数据库用户名
	private static String password="root";	//数据库用户密码
	
	/***
	 * @MethodName:  SQLUpdate 
	 * @Description:  封装SQLUpdate方法提供SQL语句及params[]参数，对数据库实现增删改功能
	 * @Params:  @param sql
	 * @Params:  @param params
	 * @Params:  @return 
	 * @Return:  boolean
	 * @Author:  伏永正
	 * @Date:  2017年10月31日下午1:21:09
	 * @throws
	 */
	public static boolean SQLUpdate(String sql,Object[] params) {
		try {
			//加载MySQL驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获得数据库连接对象
			Connection connection = DriverManager.getConnection(url, username, password);
			//调用连接对象connection的prepareStatement方法获取SQL语句预处理对象
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			//判断params数组参数的个数，并调用prepareStatement的setXXX方法赋值给？占位符
			for (int i = 1; i <= params.length; i++) {
				prepareStatement.setObject(i, params[i-1]);
			}
			//用preparedStatement的executeUpdate方法执行SQL语句
			int row = prepareStatement.executeUpdate();
			//判断是否成功
			if (row == 0) {
				return false;
			}else {
				return true;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("警告：加载MySQL驱动失败！！");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("警告：获取链接对象失败！！");
		}
	}
	/***
	 * @MethodName:  Select 
	 * @Description:  封装Select方法提供SQL语句及params[]参数，对数据库实现查询功能    无参数传null
	 * @Params:  @param sql
	 * @Params:  @param params
	 * @Params:  @return 
	 * @Return:  ResultSet
	 * @Author:  伏永正
	 * @Date:  2017年10月31日下午3:11:17
	 * @throws
	 */
	public static ResultSet Select(String sql,Object[] params) {
		try {
			//加载MySQL驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获得数据库连接对象
			Connection connection = DriverManager.getConnection(url, username, password);
			//调用连接对象connection的得preparedStatrment获取SQL语句预编译对象
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			//判断params数组参数的个数，并调用prepareStatement的setXXX方法赋值给？占位符
			if (params != null) {
				for (int i = 1; i <= params.length; i++) {
					preparedStatement.setObject(i, params[i-1]);
				}
			}
			//用preparedStatement的executeQuery方法执行SQL语句
			ResultSet resultSet = preparedStatement.executeQuery();
			 return resultSet;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("警告：加载MySQL驱动失败！！");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("警告：获取链接对象失败！！");
		}
	}

	
	
}

TestDBHelper.java

package mysql.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;


public class TestDBHelper {

	public static void main(String[] args) {
		/*//增
		String sql ="INSERT INTO admins (uname,pwd,phone) VALUES (?,?,?);";
		Object[] params ={"roor","root",789678};
		if (DBHelper.SQLUpdate(sql, params)) {
			System.out.println("添加成功");
		} else {
			System.out.println("添加失败");
		}
		//删
		String sql1 ="DELETE FROM admins WHERE uname=? ;";
		Object[] params1 ={"admin"};
		if (DBHelper.SQLUpdate(sql1, params1)) {
			System.out.println("删除成功");
		} else {
			System.out.println("删除失败");
		}
		
		//改
		String sql2 ="UPDATE admins SET uname=?,pwd=? WHERE uname=?  ;";
		Object[] params2 ={"admin","admin","root"};
		if (DBHelper.SQLUpdate(sql2, params2)) {
			System.out.println("删除成功");
		} else {
			System.out.println("删除失败");
		}
		
		//查
		String sql3 ="SELECT COUNT(*) FROM admins WHERE uname=?;";
		Object[] params2 ={"admin"};
		ResultSet select = DBHelper.Select(sql3, params2);
		try {
			if (select.next()) {
				int int1 = select.getInt(1);
				System.out.println(int1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
		String sql3 ="SELECT * FROM admins;;";
//		Object[] params2 ={"admin"};
		ResultSet select = DBHelper.Select(sql3, null);
		try {
			if (select.next()) {
				System.out.println(select.getInt("id")+"\t"+select.getString("uname"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

mysql-connector-java-5.1.39-bin.jar mysql-connector-java-5.1.39-bin.jar