Java数据库编程、XML解析技术

1 try {
2 //加载MySQL JDBC驱动
3 Class.forName("com.mysql.jdbc.Driver");
4 } catch (ClassNotFoundException e) {
5 e.printStackTrace();
6 }

1 Connection connection &＃61; null;
2 String url &＃61; "jdbc:mysql://localhost:3306/jdbc"; // 连接字符串
3 String user &＃61; "root"; // 用户名
4 String pass &＃61; "123456"; // 密码
5 try {
6 // 创建Connection对象
7 connection &＃61; DriverManager.getConnection(url, user, pass);
8 } catch (SQLException e) {
9 e.printStackTrace();
10 }

// 创建Statement对象
Statement stmt &＃61; connection.createStatement();

// 发送Insert语句
int count &＃61; stmt.executeUpdate("INSERT INTO users(username, password) VALUES (&＃39;lily&＃39;, &＃39;lily123&＃39;)");

// 发送查询SQL语句
ResultSet rs &＃61; stmt.executeQuery("SELECT id, username, password FROM users");

// 循环处理查询结果集
while (rs.next()){System.out.println("编号&＃xff1a;" &＃43; rs.getInt(1) &＃43; ", 用户名&＃xff1a;" &＃43; rs.getString("username") &＃43; ", 密码&＃xff1a;" &＃43; rs.getString("password"));
}

try {if (rs !&＃61; null)rs.close();if (stmt !&＃61; null)stmt.close();if (connection !&＃61; null)connection.close();
} catch (SQLException e) {e.printStackTrace();
}

String username &＃61; "admin";
String password &＃61; "admin";
// 创建Statement对象
Statement stmt &＃61; connection.createStatement();
// 发送SQL语句并执行
ResultSet rs &＃61; stmt.executeQuery("SELECT id, username, password FROM users WHERE username&＃61;&＃39;" &＃43; username &＃43; "&＃39; AND password&＃61;&＃39;" &＃43; password &＃43; "&＃39;");// 创建PreparedStatement对象
PreparedStatement pstmt &＃61; connection.prepareStatement("SELECT id, username, password FROM users WHERE username&＃61;? AND password&＃61;?");
// 设置pstmt参数
pstmt.setString(1, username);
pstmt.setString(2, password);
// 执行SQL语句
ResultSet rs2 &＃61; pstmt.executeQuery();

/*** 加载配置文件中JDBC连接所需要数据*/
private static void loadProperties() {InputStream in &＃61; Thread.currentThread().getClass().getResourceAsStream("/jdbc.properties");Properties prop &＃61; new Properties();try {prop.load(in);} catch (IOException e) {throw new RuntimeException(e);}driver &＃61; prop.getProperty("driver");url &＃61; prop.getProperty("url");user &＃61; prop.getProperty("user");password &＃61; prop.getProperty("password");
}

/* 通过静态代码块加载数据库驱动 */
static {loadProperties(); // 读取配置文件try {Class.forName(driver); // 加载驱动} catch (ClassNotFoundException e) {throw new RuntimeException(e);}
}

/*** 获取连接对象* * &＃64;return 连接对象* &＃64;throws SQLException* 无法建立连接&＃xff0c;则抛出异常*/
public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);
}

/*** 开启会话*/
public void open() {if (null &＃61;&＃61; conn)try {conn &＃61; JdbcResourceManager.getConnection();} catch (SQLException e) {throw new JdbcSessionException("数据库会话创建失败", e);}
}/*** 关闭数据库会话&＃xff0c;释放资源*/
public void close() {try {JdbcResourceManager.close(rs);rs &＃61; null;} catch (SQLException e) {throw new JdbcSessionException("数据库会话ResultSet关闭失败", e);} finally {try {JdbcResourceManager.close(pstmt);pstmt &＃61; null;} catch (SQLException e) {throw new JdbcSessionException("数据库会话Statement关闭失败", e);} finally {try {JdbcResourceManager.close(conn);conn &＃61; null;} catch (SQLException e) {throw new JdbcSessionException("数据库会话Connection关闭失败", e);}}}
}

/*** 执行更新SQL语句* * &＃64;param sql* 更新的SQL语句&＃xff0c;如INSERT、UPDATE、DELETE语句* &＃64;param params* 用于替换更新语句中"?"占位符的参数列表* &＃64;return 受影响行数*/
public int executeUpdate(String sql, Object... params) {try {pstmt &＃61; conn.prepareStatement(sql);if (null !&＃61; params) {for (int i &＃61; 0; i // 为每个?占位符设置参数pstmt.setObject(i &＃43; 1, params[i]);}}return pstmt.executeUpdate();} catch (SQLException e) {throw new JdbcSessionException("执行更新操作失败", e);}
}

/*** 执行查询SQL语句* * &＃64;param sql* 待执行查询的SQL语句&＃xff0c;如 SELECT 语句* &＃64;param params* 用于替换查询语句中"?"占位符的参数列表* &＃64;return 查询结果集ResultSet对象*/
public ResultSet executeQuery(String sql, Object... params) {try {pstmt &＃61; conn.prepareStatement(sql);if (null !&＃61; params) {for (int i &＃61; 0; i // 为每个?占位符设置参数pstmt.setObject(i &＃43; 1, params[i]);}}return rs &＃61; pstmt.executeQuery();} catch (SQLException e) {throw new JdbcSessionException("执行查询操作失败", e);}
}

// 将指定参数设置为给定 Reader 对象
void setCharacterStream(int parameterIndex, Reader reader) throws SQLException
// 将给定参数设置为给定 Reader 对象&＃xff0c;该对象具有给定字符数长度(int型)
void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException
// 将指定参数设置为给定 Reader 对象&＃xff0c;该对象具有给定字符数长度(long型)
void setCharacterStream(int parameterIndex, Reader reader, long length) throws SQLException

// 以 java.io.Reader 对象的形式获取此 ResultSet 对象的当前行中指定列的值
Reader getCharacterStream(int columnIndex) throws SQLException
Reader getCharacterStream(String columnLabel) throws SQLException// 以 String 的形式获取此 ResultSet 对象的当前行中指定列的值
String getString(int columnIndex) throws SQLException
String getString(String columnLabel) throws SQLException

// 将指定参数设置为给定输入流。
void setBinaryStream(int parameterIndex, InputStream x)
// 将指定参数设置为给定输入流&＃xff0c;该输入流将具有给定字节数(int型)。
void setBinaryStream(int parameterIndex, InputStream x, int length)
// 将指定参数设置为给定输入流&＃xff0c;该输入流将具有指定字节数(long型)。
void setBinaryStream(int parameterIndex, InputStream x, long length)

// 以未解释字节的流的形式获取此 ResultSet 对象的当前行中指定列的值。
InputStream getBinaryStream(int columnIndex)
// 以未解释的 byte 流的形式获取此 ResultSet 对象的当前行中指定列的值。
InputStream getBinaryStream(String columnLabel) // 以 Java 编程语言中 Blob 对象的形式获取此 ResultSet 对象的当前行中指定列的值。
Blob getBlob(int columnIndex)
// 以 Java 编程语言中 Blob 对象的形式获取此 ResultSet 对象的当前行中指定列的值。
Blob getBlob(String columnLabel)

tomlilyI will go home this weekend!

DOCTYPE mail[




]>
tomlilyI will go home this weekend!

tomlilyI will go home this weekend!

/*** 创建XML文件* * &＃64;throws IOException*/
public void createXml() throws IOException {// 创建Document对象Document document &＃61; DocumentHelper.createDocument();// 根元素节点Element root &＃61; document.addElement("students");root.addComment("all students info"); // 添加注释Element student &＃61; root.addElement("student"); // 根元素节点添加子节点student.addAttribute("id", "001"); // 添加节点属性Element name &＃61; student.addElement("name");name.addText("tommy"); // 设置节点文本Element age &＃61; student.addElement("age");age.addText("18");// 设置XML美化格式OutputFormat format &＃61; OutputFormat.createPrettyPrint();format.setEncoding("utf-8"); // 编码字符集设置// xml写入器XMLWriter writer &＃61; new XMLWriter(new FileWriter("students.xml"), format);// 写操作
writer.write(document);writer.flush();writer.close();
}

/*** 修改xml文件* * &＃64;throws Exception*/
public void modifyXml() throws Exception {// 创建SAXReader对象SAXReader reader &＃61; new SAXReader();Document document &＃61; reader.read(new File("students.xml"));/** 修改内容之一: 如果student节点中id属性的内容为001,则修改成1001。因为查找节点使用到了Xpath&＃xff0c;所以还需要将 jaxen-1.1-beta-6.jar 包添加到项目中 */List list &＃61; document.selectNodes("/students/student/&＃64;id");Iterator it &＃61; list.iterator();while (it.hasNext()) {Attribute attr &＃61; (Attribute) it.next();if ("001".equals(attr.getValue()))attr.setValue("1001");}/*** 修改内容之二: name项内容若为"tommy"&＃xff0c;则改为"tom"*/list &＃61; document.selectNodes("/students/student/name");it &＃61; list.iterator();while (it.hasNext()) {Element name &＃61; (Element) it.next();if ("tommy".equals(name.getText())) {name.setText("tom");}}/** 修改内容之三: 将id&＃61;"1001"的学生age节点删除&＃xff0c;再添加birth节点 */list &＃61; document.selectNodes("/students/student/&＃64;id");it &＃61; list.iterator();while (it.hasNext()) {Attribute attr &＃61; (Attribute) it.next();if ("1001".equals(attr.getValue())) {Element parent &＃61; attr.getParent();Iterator iterator &＃61; parent.elementIterator("age");if (iterator.hasNext()) {Element elem &＃61; (Element) iterator.next();parent.remove(elem);}Element birth &＃61; parent.addElement("birth");birth.setText("1995-01-01");break;}}/* 将document中的内容写入文件中 */// 设置XML美化格式OutputFormat format &＃61; OutputFormat.createPrettyPrint();format.setEncoding("utf-8"); // 编码字符集设置// xml写入器XMLWriter writer &＃61; new XMLWriter(new FileWriter("students.xml"), format);writer.write(document);writer.close();
}

/*** 遍历节点* * &＃64;throws Exception*/
public void readXml() throws Exception {// 创建SAXReader对象SAXReader reader &＃61; new SAXReader();// 加载xml文件Document document &＃61; reader.read(new File("students.xml"));// 获取根元素节点Element root &＃61; document.getRootElement();// 递归遍历所有节点内容
read(root);
}/*** 读节点信息* * &＃64;param element* student节点*/
private void read(Element element) {for (int i &＃61; 0, size &＃61; element.nodeCount(); i ) {Node node &＃61; element.node(i); // 获取元素的子节点if (node instanceof Comment) // 注释节点continue;if (node instanceof Element) { // 元素节点Element elem &＃61; (Element) node;System.out.print(elem.getName() &＃43; " : ");if ("student".equals(node.getName())) { // 是student节点
System.out.println();Iterator attr &＃61; elem.attributes().iterator(); // student节点属性while (attr.hasNext()) {Attribute at &＃61; (Attribute) attr.next();System.out.println(at.getName() &＃43; " : " &＃43; at.getValue());}}read((Element) node);} else if (node instanceof Text) { // 文本节点if (!"".equals(node.getText().trim()))System.out.println(node.getText());}}
}