java JDBC 增删改查 总结

java 最初对数据库进行操作,是采用 jdbc 方式,现在 市场上 的 众多框架 都对此 进行了封装,但是 我们不应该 忘记 最初的"梦想"的,下面 记录下 我前几天 写一个小程序 使用到的JDBC 操作.

本文以mysql 数据库 为例,

jar包 准备:mysql-connector-java-5.1.20-bin.jar (自行百度下载)

第一步:获取数据库 链接

package com.test.util;

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


public class JdbcUtil {

	private static String url = "jdbc:mysql://localhost:3308/test?useUnicode=true&characterEncoding=UTF-8";// 目标数据库
	private static String username = "root";//账号
	private static String password = "123456";//密码
	private static String driver = "com.mysql.jdbc.Driver";//驱动
	private static Connection conn;// 链接 ,静态保存,不可关闭,以后使用呢

	static {
		try {
			Class.forName(driver); 
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("(加载驱动)JDBC获取数据库链接失败:"+e.getMessage());
			conn = null;
			if(driver.equals(e.getMessage())){
				try {
					System.out.println("(不加载驱动)重新获取链接");
					conn = DriverManager.getConnection(url, username, password);
				} catch (SQLException e1) {
					e1.printStackTrace();
					System.out.println("(不加载驱动)重新获取链接失败:"+e1.getMessage());
					conn = null;
				}
			}
		}
		
		
		
	}
	
	public static Connection getConnection () {
		
		return conn;
	}
	
	public static void close(Connection con){
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
			
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) {
		JdbcUtil jdbcUtil = new JdbcUtil();
		Connection connection = jdbcUtil.getConnection();
		System.out.println(connection);
	}
}

运行后,要是出现类似结果 则说明 已经获取成功:

com.mysql.jdbc.JDBC4Connection@23ab930d

以下 是对 数据库表 进行增删改查的 基本操作;

数据库信息dll:

/*DDL 信息*/------------

CREATE TABLE `allpeople` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '参选人员姓名',
  `isSelected` int(11) DEFAULT '0' COMMENT '0:没有被选中;1:已结被选中了',
  `selectTime` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

第二: 建立 对应pojo 实体类

package com.test.data;

public class AllPeople {

	private int id;
	private String name;
	private int isSelect;
	private String selectTime;
	getting  setting 方法.......
	
}

第三:操作

package com.test.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.springframework.stereotype.Service;

import com.lottery.data.AllPeople;
import com.lottery.util.JdbcUtil;
import com.mysql.jdbc.PreparedStatement;

@Service("peopleService")
public class PeopleService {

	/**
	 *  初始化数据 导入,把图片数据(all 中) 全部导入 数据库
	 * @param allPepple
	 */
	public void initData(List<String>  allPepple){
		Connection con = JdbcUtil.getConnection();
		try {
			Statement statemenet = con.createStatement();
			if(allPepple != null && allPepple.size() > 0){
				for (String name : allPepple) {
					String query = "insert into allpeople (name, isSelected) values('"
				            + name + "','" + 0 + "')";
				    statemenet.addBatch(query);

				}
			}
			
			int[] number = statemenet.executeBatch();
			System.out.println("初始化 共插入 数据:"+number.length +"条!");
			statemenet.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//JdbcUtil.close(con);
		}
	}
	/**
	 * 抽中后 ,更新状态
	 * @param name
	 */
	public void updateInfo(String name ,int isSelected){
		Connection con = JdbcUtil.getConnection();
		String insertTime ="";
		if(isSelected == 1){
			SimpleDateFormat now = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String nowTime = now.format( new Date() ); 
		
			insertTime = nowTime;
			 
		}
	 String sql ="update allpeople SET selectTime = '"+insertTime+"',isSelected = '" + isSelected + "' where name='" + name + "'";
	 PreparedStatement pstmt;
	
	    try {
	        pstmt = (PreparedStatement) con.prepareStatement(sql);
	        pstmt.executeUpdate();
	        pstmt.close();
	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }finally{
	    	//JdbcUtil.close(con);
	    }
	   
	}

	/**
	 * 
	 * @param isSelected 1: 选择  0:没有选中
	 * @return
	 */
	public List<String> queryList(int isSelected){
		Connection con = JdbcUtil.getConnection();
		List<String> list = new ArrayList<String>();
		String sql = "select * from allpeople where isSelected = ?";
		PreparedStatement pstmt;

		try {
			//根据sql语句创建预处理对象
			pstmt = (PreparedStatement) con.prepareStatement(sql);
			
			pstmt.setObject(1, isSelected);
			//执行更新
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
			
				String name=rs.getString("name");
				list.add(name);
			}
			pstmt.close();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		
			}finally{
				//JdbcUtil.close(con);
			}
		// 乱序
			if(isSelected == 0 && list.size()>0){
				List<String> random = new ArrayList<String>(list.size());  
			    do {  
			        int index = Math.abs(new Random().nextInt(list.size()));  
			        random.add(list.remove(index));  
			  
			    } while (list.size() > 0);
			    list= random;
			}
		return list;
}
	public void clearData(){
		Connection con = JdbcUtil.getConnection();
		try {
			Statement sta = con.createStatement();
			String sql = "TRUNCATE TABLE allpeople";
			sta.execute(sql);
			sta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

谢谢观看!!!