myeclipse链接mysql以及简单的sql语句操作，笔记

1.在myeclipse中添加mysql驱动点击打开链接下载驱动

2.创建数据表点击打开链接下载数据表

3.先看一下myeclipse的目录树

4.在po包中写实体类user.java

public class User {
	private int userid;
	private String username;
	private String password;
	private int sex;
	private String flag;
	public int getUserid() {
		return userid;
	}
	public void setUserid(int userid) {
		this.userid = userid;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getSex() {
		return sex;
	}
	public void setSex(int sex) {
		this.sex = sex;
	}
	public String getFlag() {
		return flag;
	}
	public void setFlag(String flag) {
		this.flag = flag;
	}


}

5.新建一个file文件命名为jdbc.properties

6.在util包写工具类DBconn.java

public class DBconn {
	//驱动
		private static String DBDriver="";
		private static String DBURL="";
		private static String DBUser="";
		private static String DBPassword="";
		static {
			InputStream is =DBconn.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties p=new Properties();
			try {
				p.load(is);
				DBDriver=p.getProperty("DBDriver");
				DBURL=p.getProperty("DBURL");
				DBUser=p.getProperty("DBUser");
				DBPassword=p.getProperty("DBPassword");
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}//只需要执行一次
		public static Connection getConnetctio()
		{
			Connection conn=null;
			try {
				Class.forName(DBDriver);
				try {
					conn=DriverManager.getConnection(DBURL,DBUser,DBPassword);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return conn;
		}
		
		public static void close(Statement st,Connection conn){
			//先产生后关闭
			if(st!=null)
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

7.在dao包中写UserDao.java

public class UserDao {
	//单例模式
	public UserDao(){
		
	}
	public static UserDao userdao=new UserDao();
	public static UserDao getIntance()
	{
		return userdao;
	}
	
	public boolean saveUser(User user) throws SQLException
	{
		boolean flag=false;
		Connection conn=null;
		Statement st=null;
		try {
			conn=DBconn.getConnetctio();
			String sql ="insert into user(username,password,flag) values('"+user.getUsername()+"','"+user.getPassword()+"','"+user.getFlag()+"')";
			st=conn.createStatement();
			int rows=st.executeUpdate(sql);
			if(rows>0)
			{
				flag=true;
			}
			System.out.println(sql);
		}
		finally
		{
			DBconn.close(st,conn);
		}
		return flag;
	}
	
	public boolean deleteUserById(int userid) throws SQLException 
	{
		boolean boo=false;
		Connection conn=null;
		Statement st=null;
		try {
		conn=DBconn.getConnetctio();
		String sql ="delete from user where userid="+userid;
		st=conn.createStatement();
		int rows=st.executeUpdate(sql);
		if(rows>0)
		{
			 boo=true;
		}
		}
		finally
		{
			DBconn.close(st,conn);
		}
		return boo;
	}
	
	public boolean updateUserById(Map<String, Object> map) throws SQLException 
	{
		boolean boo=false;
		Connection conn=null;
		Statement st=null;
		try {
		conn=DBconn.getConnetctio();
		String sql ="update user set username='"+(String)map.get("username")+"' where userid="+(Integer)map.get("userid");
		st=conn.createStatement();
		int rows=st.executeUpdate(sql);
		if(rows>0)
		{
			 boo=true;
		}
		}
		finally
		{
			DBconn.close(st,conn);
			
		}
		return boo;
	}

8.在test中实现测试类UserDaoTest.java（为junit）

public class UserDaoTest {

	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
	}

	@Test
	public void test() throws SQLException {
		//单例模式
		UserDao userdao=UserDao.getIntance();
		User user=new User();
		user.setUsername("kiki");
		user.setPassword("1212");
		System.out.println(userdao.saveUser(user) ? "保存成功":"保存失败");
	}
	
	@Test
	public void deleteUserById() throws Exception
	{
		UserDao userdao=UserDao.getIntance();
	   System.out.println(userdao.deleteUserById(6));	
	}
	
	@Test
	public void updateUserById() throws Exception
	{
		Map<String, Object> map=new HashMap<String, Object>();
		map.put("username", "tr");
		map.put("userid", 7);
		UserDao userdao=UserDao.getIntance();
	   System.out.println(userdao.updateUserById(map)?"修改成功":"修改失败");	
	}

}

详细的代码请参照：http://download.youkuaiyun.com/download/qq_28316183/9968855