jdbc插入删除更新db代码

1.插入删除代码如下

public void writeDB(Map<String,Object> map) throws SQLException, ClassNotFoundException, InterruptedException{
		synchronized (lock) {
			System.out.println();
			String url="jdbc:mysql://localhost:3306/mydbname?useUnicode=true&characterEncoding=UTF-8";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn=DriverManager.getConnection(url,"root","");    
	        //删除
			String sqldel = "delete from spidercontent";
			PreparedStatement stmtdel = conn.prepareStatement(sqldel); 
			stmtdel.executeUpdate();
			stmtdel.close();
			//插入
			String sql = "insert into spidercontent(page,namelink,description,kind)values(?,?,?,?)";
	        PreparedStatement stmt = conn.prepareStatement(sql);  
	        for(Entry<String, Object> mapitem :map.entrySet()){
				for(String item : (ArrayList<String>) mapitem.getValue()){
					stmt.setString(1, mapitem.getKey().split("/")[6]);
					stmt.setString(2, item);
					stmt.setString(3, "");
					stmt.setString(4, "");
					stmt.addBatch();
				}
			}  
	        stmt.executeBatch();	
	        conn.close(); 
	        stmt.close();
		}
	}

第四句不加，插入的中文是乱码

2.插入带自增字段的DB

不能用上面的setInt 或者setString，只能组织出sql再

Class.forName(driverClassName);
		Connection conn=DriverManager.getConnection(dburlPrefix+"?useUnicode=true&characterEncoding=UTF-8",username,password);      
        //插入  
        //String sql = "insert into t_ptmachine(ip,cpu,mem,use_or_not,login_username,login_pass,operate_username,remark)values(?,?,?,?,?,?,?,?)";  
		String sql = "insert into t_ptmachine(cpu,mem,use_or_not,login_username,login_pass,operate_username,remark)values('a','a','a','a','a','a','a')";  
        PreparedStatement stmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);   
        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys(); /*获取主键增长列的结果集*/  
        while (rs.next()) {  
            System.out.println(rs.getInt(1));  /*返回主键自增主键是多少*/  
        }  

3.DB中是int 和 Date类型的数据的插入

不带自增主键，用setInt  setDate（同第一点）

带自增主键，用sql拼接（同第二点）注意代码中的三处红色，并且这里的Date.valueOf 是java.sql.Date不是java.util.Date，而且这个日期只记录年月日

String sql = "insert into t_lptmachine(cpu,mem,use_or_not,login_username,login_pass,operate_username,date) values ('a',5,'a','a','a','a','" + Date.valueOf(("2015-12-17")) +" ')"; 

如果想记录时分秒，用Timestamp

sql = "UPDATE t_ptservice_info SET test_begin_time='"+Timestamp.valueOf("1989-09-09 11:11:11")+"' WHERE task_id="+task.getTaskId();

4.jdbc  update

String sql = "UPDATE t_lptmachine SET cpu= 'changed' ,mem='changed' WHERE taskid ="+taskid;   
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.executeUpdate();

5.insert  空值，只针对db中可以为空的字段，注意不是空串  ‘’ 

String sql = "insert into t_lptmachine(cpu,mem,use_or_not,login_username,login_pass,operate_username,date) values ('a',5,'a','a','a','a',<span style="color:#ff0000;">null</span>)";