2020-10-13(JDBC基本练习)

• 增加

package demo;

//增加

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

public class Demo01 {
	public static void main(String[] args) {
		//1.导jar包(驱动)
		//2.加载驱动
		Connection con = null;
		Statement stm = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//3.创建链接
			String url = "jdbc:mysql://localhost:3306/dvd";
			String user = "root";
			String password = "1234";
			con = DriverManager.getConnection(url, user, password);
			//4.获得sql执行器，执行sql
			stm = con.createStatement();
			String sql = "INSERT INTO dvd_1(id,DVD_name,type,state,lend_time,return_time) VALUES ('CD0010','爱情公寓3','爱情片',1,'2019-09-15 15:15:25',null);";
			int executeUpdate = stm.executeUpdate(sql);
			if (executeUpdate == 1) {
				System.out.println("添加成功");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stm != null && con != null) {
				try {
					stm.close();
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		}
	}

}

• 删除

package demo;

//删除

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

public class Demo02 {
	public static void main(String[] args) {
		// 1.jar包
		// 2.加载驱动
		Connection con = null;
		Statement sta = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// 3.连接
			String url = "jdbc:mysql://localhost:3306/dvd";
			String user = "root";
			String password = "1234";
			con = DriverManager.getConnection(url, user, password);
			// 4.获得sql执行器，执行sql
			sta = con.createStatement();

			String sql = "DELETE FROM dvd_1 WHERE id='CD0010';";
			int executeUpdate = sta.executeUpdate(sql);

			if (executeUpdate == 1) {
				System.out.println("删除成功");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (sta != null) {
					sta.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}

• 修改

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

//修改

public class Demo03 {
	public static void main(String[] args) {
		//1.导包
		//2.加载驱动
		Connection con = null;
		Statement sta = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//3.连接
			String url = "jdbc:mysql://localhost:3306/dvd";
			String user ="root";
			String password = "1234";
			con = DriverManager.getConnection(url, user, password);
			//4.sql执行器,执行sql
			sta = con.createStatement();
			String sql = "UPDATE dvd_1 SET DVD_name='电锯惊魂' WHERE DVD_name='2012';";
			int executeUpdate = sta.executeUpdate(sql);
			if (executeUpdate == 1) {
				System.out.println("修改成功");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (sta != null) {
					sta.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
	}
}

• 查找

package demo;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

//查

public class Demo04 {
	public static void main(String[] args) {
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		boolean status = true;
		ArrayList<DVD> list = new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url ="jdbc:mysql://localhost:3306/dvd";
			String user = "root";
			String password = "1234";
			con = DriverManager.getConnection(url, user, password);
			
			sta = con.createStatement();
			
			String sql = "SELECT * from dvd_1 ;";
			rs = sta.executeQuery(sql);
			
			//把查询之后返回的ResultSet rs 存储到list集合中去,方便操作
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("DVD_name");
				String type = rs.getString("type");
				
				//DVD对象中的状态为boolean类型 
				int s = rs.getInt("state");
				if (s == 1) {
					status = true;
				} else if (s == 0) {
					status = false;
				}
				/**
				 * 获取时间类型的方法，
				 * 1.getTimestamp()获取年月日时分秒
				 * 2.getDate()只能获取年月日，时分秒会默认为00:00:00
				 * 3.注意获取的时间可能为null
				 */
				Timestamp timestamp = rs.getTimestamp("lend_time");
				Timestamp timestamp2 = rs.getTimestamp("return_time");
				java.util.Date lendTime = null;
				java.util.Date returnTime = null;
				if (timestamp == null) {
					lendTime = null;
				}else {
					lendTime = new Date(timestamp.getTime());
				}
				if (timestamp2 == null) {
					returnTime = null;
				}else {
					returnTime = new Date(timestamp2.getTime());
				}
				DVD d = new DVD(id, name, type, status, lendTime, returnTime);
				list.add(d);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (sta != null) {
					sta.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		for (DVD dvd : list) {
			System.out.println(dvd.toString());
		}
		
	}
}