jdbc的增删改查实例

1.创建连接

Class.forName("com.mysql.jdbc.Driver");

2.连接数据库

conn = DriverManager.getConnection(url,user,password);

具体函数代码

public static void creatConn(){
		try {
			conn = null;
			String url = "jdbc:mysql://localhost:3306/demo1";
			String user = "root";
			String password = "123456";
			//选择数据库，加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			//连接数据库
			conn = DriverManager.getConnection(url,user,password);
			Statement s = conn.createStatement();
			//关闭自动提交
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动没有找到");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		
	}

进行增加的操作

private static void exeUpdate(String code, String name) {
	//设置初始值
		PreparedStatement ps = null;
		try {
			//创建数据库查询
			ps = conn.prepareStatement("insert into demo(code,name) VALUES(?,?);");
			ps.setString(1, code);
			ps.setString(2, name);
			//获取查询结果
			int i = ps.executeUpdate();
			System.out.println(i);
			//抛出异常
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

进行查询和删除的操作（较为底层的方法，实际项目中直接框架实现）

private static void exeQuery(String name) {
		PreparedStatement ps = null;
		try {
		//使用占位符
			ps = conn.prepareStatement("select * FROM demo where id < ?;");
			//给占位符赋值，将程序写活
			ps.setString(1, name);
			//打印出查询的字段
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String nam = rs.getString("name");
				String code = rs.getString("code");
				System.out.println("id = "+id+",code = "+code+",name = "+nam+"");
				
				//删除，此处删除直接在查询后面执行，查出来就删掉因此使用了事物的功能
				//事物的多个操作，要么都成功，要么都失败，在最后直接答应出结果
				PreparedStatement str = conn.prepareStatement("delete from demo where id = ?;");
				str.setInt(1,id);
				str.executeUpdate();
			}
			//关闭事物
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			//关闭查询
			try {
				if (null != ps) {
					ps.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

上完整代码

package com.Clients.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Test {
	
	private static Connection conn;
	
	public static void main(String[] args) {
		//创建连接
		creatConn();
		
		//生成单位
		for (int i = 1; i <= 20; i++) {
			String str = String.format("%02d",i);
			exeUpdate("英雄编号" + str,"hero" + str);
		}
		
		//循环执行查询
		while(true){
			System.out.println("是否删除前十条数据");
			Scanner sca = new Scanner(System.in);
			String a = sca.nextLine();
			if (a.equals("yes")) {
				exeQuery("843");
				break;
			}else if (a.equals("no")) {
				break;
			}else{
				
			}
		}
		
		closeConn();
		
	}

	private static void exeQuery(String name) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("select * FROM demo where id < ?;");
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String nam = rs.getString("name");
				String code = rs.getString("code");
				System.out.println("id = "+id+",code = "+code+",name = "+nam+"");
				
				//删除
				PreparedStatement str = conn.prepareStatement("delete from demo where id = ?;");
				str.setInt(1,id);
				str.executeUpdate();
			}
			//关闭事物
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			//关闭查询
			try {
				if (null != ps) {
					ps.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void exeUpdate(String code, String name) {
		PreparedStatement ps = null;
		try {
			//创建数据库查询
			ps = conn.prepareStatement("insert into demo(code,name) VALUES(?,?);");
			ps.setString(1, code);
			ps.setString(2, name);
			//获取查询结果
			int i = ps.executeUpdate();
			System.out.println(i);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void creatConn(){
		try {
			conn = null;
			String url = "jdbc:mysql://localhost:3306/demo1";
			String user = "root";
			String password = "123456";
			//选择数据库，加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			//连接数据库
			conn = DriverManager.getConnection(url,user,password);
			Statement s = conn.createStatement();
			//关闭自动提交
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动没有找到");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	//关闭连接
	private static void closeConn() {
		try {
			if (null != conn) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}