Java中的数据库处理—增删改查

本文转载地址为：http://blog.csdn.net/to_be_designer/article/details/4740

连接数据库

　　Java对数据库进行处理，首先要连接数据库。 
连接数据库步骤： 
1. 加载驱动程序。 
2. 创建Connection对象。 
3. 连接数据库成功。 
4. 数据库使用结束后销毁Connection对象。 
5. 释放连接。

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//连接数据库的驱动</span>
String driver = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"com.mysql.jdbc.Driver"</span>;
<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//指向数据库访问地址</span>
String url = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"jdbc:mysql://localhost:3306/clazz"</span>;
<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//数据库的用户名</span>
String user = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"root"</span>;
<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//数据库用户密码</span>
String password = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"123456"</span>;         
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//加载驱动</span>
    Class.forName(driver);
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//与数据库建立连接</span>
    Connection conn = DriverManager.getConnection(url,user, password);
    } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (ClassNotFoundException e) {            
        e.printStackTrace();
    } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
        e.printStackTrace();
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li></ul>

操作数据库

简单数据库的步骤： 
1. 创建Statement操作类的对象。 
2. 定义SQL操作语句。 
3. 通过Statement对象执行SQL语句。

创建数据表

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>(!conn.isClosed()){
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库操作类</span>
    Statement sta = conn.createStatement();
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*创建一个表。*/</span>
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//定义SQL语句</span>
    String  create = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"create TABLE if not exists user(id int NOT NULL PRIMARY KEY auto_increment, name varchar(30) NOT NULL , password varchar(30) NOT NULL)"</span>;
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行SQL语句</span>
    sta.execute(create);
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"创建表格完毕！"</span>);
}<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">else</span>{
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"请打开数据库连接！"</span>);
}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li></ul>

增

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库操作类</span>
    Statement sta = conn.createStatement();
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*增*/</span>
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//定义SQL语句</span>
    String sql_1 = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"insert into student(name, sex, age)values('王五', 1, 20)"</span>;
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行SQL语句</span>
    sta.execute(sql_1);
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"添加记录成功！"</span>);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

删

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库操作类</span>
    Statement sta = conn.createStatement();
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*删*/</span>
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//定义SQL语句</span>
    String sql_2 = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"delete from student where name ='王五'"</span>;
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行SQL语句</span>
    sta.execute(sql_2);
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"删除记录成功！"</span>);  </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

改

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库操作类</span>
    Statement sta = conn.createStatement();
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*改*/</span>
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//定义SQL语句</span>
    String sql_3 = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"update student set age = 40 where name ='李四'"</span>;
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行SQL语句</span>
    sta.execute(sql_3);
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"更改记录成功！"</span>);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

查

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库操作类</span>
    Statement sta = conn.createStatement();
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*查*/</span>
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//定义SQL语句</span>
    String sql_select = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"select * from student"</span>;
    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//将查询的结果保存到set中。</span>
    ResultSet set = sta.executeQuery(sql_select);
    set.first();<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//将游标移到第一位。</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">while</span>(!set.isAfterLast()){<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//判断是否到达最后一位</span>
        System.out.println(set.getString(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name"</span>)+<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">" "</span>+set.getString(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"age"</span>)+<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">" "</span>+set.getShort(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"sex"</span>));
        set.next();
    }
    System.out.println(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"查找数据成功！"</span>);  </code>