jdbc

https://www.cnblogs.com/erbing/p/5805727.html   介绍jdbc

//测试类

package com.wxx.test.jdbctest.main;

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

import com.wxx.test.jdbctest.domain.Info;
import com.wxx.test.jdbctest.util.DBUtils;

public class MainClass {
	public static void main(String[] args) {
		Connection conn = DBUtils.getConnection();
		try {
			// add
			Info info1 = new Info("001", "123", 18);
			Info info2 = new Info("002", "123", 18);
			DBUtils.save(info1);

			// test Transaction
			/*int i = 1 / 0;
			DBUtils.save(info2);*/
			// delete
			String id = "001";
			DBUtils.delete("001");
			// update
			info2.setPass("123456");
			DBUtils.update(info2);
			// query
			DBUtils.queryAll();
			conn.commit();
		} catch (SQLException e) {
			System.out.println("添加失败");
			e.printStackTrace();
		}

	}
}

//实体类

package com.wxx.test.jdbctest.domain;

public class Info {

	private String id;
	private String pass;
	private int age;

	public Info(String id, String pass, int age) {
		super();
		this.id = id;
		this.pass = pass;
		this.age = age;
	}

	public Info() {
		super();
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Override
	public String toString() {
		System.out.println("id : " + this.getId() + ",pass : " + this.getPass()
				+ ",age:" + this.getAge());
		return super.toString();
	}

}

工具类

package com.wxx.test.jdbctest.util;

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

import com.wxx.test.jdbctest.domain.Info;

public class DBUtils {

	private static Connection conn;
	private static String root = "root";
	private static String pass = "mysql";

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载驱动类
			// 数据库连接地址的形式由三部分组成：
			// a.jdbc协议：JDBC URL中的协议总是jdbc；

			// b.子协议：驱动程序名和数据库连接机制（这种机制可由一个或多个驱动程序支持）的名称，例如:mysql;

			// c.子名称：一种标识数据库的方法。必须遵循"/主机名:端口/子协议"的标准URL命名约定，例如：//localhost:3306/jdbctest.
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jdbctest", root, pass);// 获取连接
			conn.setAutoCommit(false);// 设置不自动提交事务，默认自动提交
		} catch (ClassNotFoundException e) {
			System.out.println("驱动类没有找到，请检查是否成功导入jdbc驱动包");
		} catch (SQLException e) {
			System.out.println("连接数据库失败,请检查数据库服务是否启动");
		}
	}

	public static Connection getConnection() {
		return conn;
	}

	/**
	 * 增加
	 * 
	 * @param info
	 * @throws SQLException
	 */
	public static void save(Info info) throws SQLException {
		String saveSql = "insert into info values(?,?,?)";
		// 使用PreparedStatement比PreparedStament的优势
		// 1、PreparedStement会先初始化sql，将sql语句先提交到数据库中进行预编译。当多次使用的时候效率比较高。
		// 2、防止sql注入
		// 3、可读性比较高
		PreparedStatement ps = conn.prepareStatement(saveSql);
		ps.setString(1, info.getId());
		ps.setString(2, info.getPass());
		ps.setInt(3, info.getAge());
		ps.executeUpdate(); // 当增删改的时候使用executeUpate方法
		System.out.println("插入成功");
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @throws SQLException
	 */
	public static void delete(String id) throws SQLException {
		String deleteSql = "delete from info where id = ?";
		PreparedStatement ps = conn.prepareStatement(deleteSql);
		ps.setString(1, id);
		ps.executeUpdate();
		System.out.println("删除成功");
	}

	/**
	 * 修改
	 * 
	 * @throws SQLException
	 */
	public static void update(Info info) throws SQLException {
		String updateSql = "update info set pass= ?,age= ? where id =?";
		PreparedStatement ps = conn.prepareStatement(updateSql);
		ps.setString(1, info.getPass());
		ps.setInt(2, info.getAge());
		ps.setString(3, info.getId());
		ps.executeUpdate();
		System.out.println("修改成功");
	}

	/**
	 * 查询
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static List<Info> queryAll() throws SQLException {
		List<Info> infos = new ArrayList<Info>();
		String querySql = "select * from info";
		PreparedStatement ps = conn.prepareStatement(querySql);
		ResultSet rs = ps.executeQuery();// 查询使用executeQuery方法
		System.out.println();
		while (rs.next()) {
			Info info = new Info();
			info.setId(rs.getString(1));
			info.setPass(rs.getString(2));
			info.setAge(rs.getInt(3));
			infos.add(info);
		}
		System.out.println(infos);
		return infos;
	}
}