java与数据库的相关小知识

1,软编码链接数据库

public class DBManager {
	
	private Connection con;
	
	public Connection getCon() {
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;
		
		InputStream in = 
				getClass().getClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		try {
			properties.load(in);
			driverClass = properties.getProperty("driver");
			jdbcUrl = properties.getProperty("jdbcUrl");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
			
			Driver driver = 
					(Driver) Class.forName(driverClass).newInstance();
			
			Properties info = new Properties();
			info.put("user", user);
			info.put("password", password);
			
			//通过 Driver 的 connect 方法获取数据库连接. 
			con = driver.connect(jdbcUrl, info);
			
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		
		return con;
	}
	
	
	
	public void close() {
		if(con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				System.out.println("数据库链接关闭失败");
				/*// TODO Auto-generated catch block
				e.printStackTrace();*/
			}
		}
	}
}

2，使用PreparedStatement

public User getUser(User aUser) throws SQLException, ClassNotFoundException {
		User user = null;
		String username = aUser.getUsername();
		String password = aUser.getPassword();
		
		 //1 加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        
       ///2 获取数据库连接
        String url = "jdbc:mysql://127.0.0.1:3307/javaweb_work?useUnicode=true&characterEncoding=UTF-8";
        String auser = "root";
        String apassword = "123";
        Connection conn = DriverManager.getConnection(url, auser, apassword);
		
        PreparedStatement preStatement = conn.prepareStatement("SELECT* FROM user WHERE username=? AND password=?");
        preStatement.setString(1, username);
        preStatement.setString(2, password);
 
        ResultSet result = preStatement.executeQuery();
 
        while(result.next()){
            user = new User(result.getString("username"), result.getString("passwoed"), result.getString("nickname"));
        }
        
        return user;
	}

2，在javaWeb中最重要的还是看webRoot中的lib文件夹里有没有jar包。

1.如何在java中为java.sql.Date类型的值赋值：

Date date = Date.valueOf("1999-01-02");

（https://blog.csdn.net/DAOYUANWANG/article/details/51713640 ，可参考，但不能用）

2.使用变量生成sql语句时，注意要用单引号！

String sql = "INSERT INTO `学生表`\r\n" +
        "VALUES('" + id + "', '" + name + "','" + sex + "','" + dat + "','" + prco + "','" + clas + "')";

（https://zhidao.baidu.com/question/203170757.html）

3.姓名（表中的属性）不能加单引号，但学生表需要加单引号

String sql = "DELETE FROM `学生表`" +
                "WHERE 姓名 = '" + name + "'";

4.加载驱动程序： Class.forName(DBDRIVER);

public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";