详解spring开发_JDBC操作MySQL数据库

本文介绍spring开发_jdbc操作mysql数据库，具体如下：

项目结构：

数据库表：

/spring_1100_spring+jdbc/src/com/b510/bean/person.java

package com.b510.bean;

/**
 * 普通的javabean类person
 * 
 * @author hongten
 * 
 */
public class person {

  /**
   * id号
*/
  private int id;
  /**
   * 姓名
*/
  private string name;
  /**
   * 年龄
*/
  private int age;
  /**
   * 性别
*/
  private string sex;


  public person(int id, string name, int age, string sex) {
    this.id = id;
    this.name = name;
    this.age = age;
    this.sex = sex;
  }

  public person() {
  }

  public int getid() {
    return id;
  }

  public void setid(int id) {
    this.id = id;
  }

  public string getname() {
    return name;
  }

  public void setname(string name) {
    this.name = name;
  }

  public int getage() {
    return age;
  }

  public void setage(int age) {
    this.age = age;
  }

  public string getsex() {
    return sex;
  }

  public void setsex(string sex) {
    this.sex = sex;
  }

}

/spring_1100_spring+jdbc/src/com/b510/service/personservice.java

package com.b510.service;

import java.util.list;

import com.b510.bean.person;

public interface personservice {

  /**
   * 保存person
   * 
   * @param person
*/
  public abstract void save(person person);

  /**
   * 更新person
   * 
   * @param person
*/
  public abstract void update(person person);

  /**
   * 获取person
   * 
   * @param id
   * @return
*/
  public abstract person getperson(integer id);

  /**
   * 获取所有person
   * 
   * @return
*/
  public abstract list<person> getperson();

  /**
   * 删除指定id的person
   * 
   * @param id
*/
  public abstract void delete(integer id);

}

/spring_1100_spring+jdbc/src/com/b510/service/impl/personservicebean.java

package com.b510.service.impl;

import java.util.list;

import javax.sql.datasource;

import org.springframework.jdbc.core.jdbctemplate;

import com.b510.bean.person;
import com.b510.service.personservice;

/**
 * 业务bean
 * 
 * @author hongten
 * 
 */
public class personservicebean implements personservice {

  /**
   * 数据源
*/
  private datasource datasource;
  /**
   * spring提供的jdbc操作辅助类
*/
  private jdbctemplate jdbctemplate;

  // 设置数据源
  public void setdatasource(datasource datasource) {
    this.jdbctemplate = new jdbctemplate(datasource);
  }

  public void save(person person) {
    jdbctemplate.update("insert into person(name,age,sex)values(?,?,?)",
        new object[] { person.getname(), person.getage(),
            person.getsex() }, new int[] { java.sql.types.varchar,
            java.sql.types.integer, java.sql.types.varchar });
  }

  public void update(person person) {
    jdbctemplate.update("update person set name=?,age=?,sex=? where id=?",
        new object[] { person.getname(), person.getage(),
            person.getsex(), person.getid() }, new int[] {
            java.sql.types.varchar, java.sql.types.integer,
            java.sql.types.varchar, java.sql.types.integer });

  }

  public person getperson(integer id) {
    person person = (person) jdbctemplate.queryforobject(
        "select * from person where id=?", new object[] { id },
        new int[] { java.sql.types.integer }, new personrowmapper());
    return person;

  }

  @suppresswarnings("unchecked")
  public list<person> getperson() {
    list<person> list = jdbctemplate.query("select * from person", new personrowmapper());
    return list;

  }

  public void delete(integer id) {
    jdbctemplate.update("delete from person where id = ?", new object[] { id },
        new int[] { java.sql.types.integer });

  }
}

/spring_1100_spring+jdbc/src/com/b510/service/impl/personrowmapper.java

package com.b510.service.impl;

import java.sql.resultset;
import java.sql.sqlexception;

import org.springframework.jdbc.core.rowmapper;

import com.b510.bean.person;

public class personrowmapper implements rowmapper {

  @override
  public object maprow(resultset set, int index) throws sqlexception {
    person person = new person(set.getint("id"), set.getstring("name"), set
        .getint("age"), set.getstring("sex"));
    return person;
  }

}

/spring_1100_spring+jdbc/src/com/b510/test/springjdbctest.java

package com.b510.test;

import java.util.list;

import org.springframework.context.applicationcontext;
import org.springframework.context.support.classpathxmlapplicationcontext;

import com.b510.bean.person;
import com.b510.service.personservice;

public class springjdbctest {

  public static void main(string[] args) {
    applicationcontext act = new classpathxmlapplicationcontext("bean.xml");

    personservice personservice = (personservice) act
        .getbean("personservice");

    person person = new person();
    person.setname("苏东坡");
    person.setage(21);
    person.setsex("男");

    // 保存一条记录
    personservice.save(person);

    list<person> person1 = personservice.getperson();
    system.out.println("++++++++得到所有person");
    for (person person2 : person1) {
      system.out.println(person2.getid() + " " + person2.getname()
          + "  " + person2.getage() + " " + person2.getsex());
    }
    person updateperson = new person();
    updateperson.setname("divide");
    updateperson.setage(20);
    updateperson.setsex("男");
    updateperson.setid(5);
    // 更新一条记录
    personservice.update(updateperson);
    system.out.println("******************");

    // 获取一条记录
    person oneperson = personservice.getperson(2);
    system.out.println(oneperson.getid() + " " + oneperson.getname()
        + " " + oneperson.getage() + " " + oneperson.getsex());
    // 删除一条记录
    personservice.delete(1);
  }
}

/spring_1100_spring+jdbc/src/bean.xml

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:context="http://www.springframework.org/schema/context"
  xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
  xsi:schemalocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
  <!--配置数据源 -->
  <bean id="datasource" class="org.apache.commons.dbcp.basicdatasource"
    destroy-method="close">
    <property name="driverclassname" value="org.gjt.mm.mysql.driver" />
    <property name="url"
      value="jdbc:mysql://localhost:3307/spring?useunicode=true&characterencoding=utf-8" />
    <property name="username" value="root" />
    <property name="password" value="root" />
    <!-- 连接池启动时的初始值 -->
    <property name="initialsize" value="1" />
    <!-- 连接池的最大值 -->
    <property name="maxactive" value="300" />
    <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxidle为止 -->
    <property name="maxidle" value="2" />
    <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->
    <property name="minidle" value="1" />
  </bean>
  <!--
    采用注解方式来配置事务。针对数据源的事务管理器
    ，把我们定义的数据源注入到datasourcetransactionmanager类的属性datasource中
  -->
  <bean id="txmanager"
    class="org.springframework.jdbc.datasource.datasourcetransactionmanager">
    <property name="datasource" ref="datasource" />
  </bean>
  <!--
    引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx
    2.http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
    采用@transaction注解方式使用事务管理器
  -->
  <tx:annotation-driven transaction-manager="txmanager" />

  <!-- 配置业务bean：personservicebean -->
  <bean id="personservice" class="com.b510.service.impl.personservicebean">
    <!-- 向属性datasource注入数据源 -->
    <property name="datasource" ref="datasource"></property>
  </bean>
</beans>

运行结果；

2012-3-9 23:30:57 org.springframework.context.support.abstractapplicationcontext preparerefresh
信息: refreshing org.springframework.context.support.classpathxmlapplicationcontext@1a05308: display name [org.springframework.context.support.classpathxmlapplicationcontext@1a05308]; startup date [fri mar 09 23:30:57 cst 2012]; root of context hierarchy
2012-3-9 23:30:57 org.springframework.beans.factory.xml.xmlbeandefinitionreader loadbeandefinitions
信息: loading xml bean definitions from class path resource [bean.xml]
2012-3-9 23:30:58 org.springframework.context.support.abstractapplicationcontext obtainfreshbeanfactory
信息: bean factory for application context [org.springframework.context.support.classpathxmlapplicationcontext@1a05308]: org.springframework.beans.factory.support.defaultlistablebeanfactory@2bb514
2012-3-9 23:30:58 org.springframework.beans.factory.support.defaultlistablebeanfactory preinstantiatesingletons
信息: pre-instantiating singletons in org.springframework.beans.factory.support.defaultlistablebeanfactory@2bb514: defining beans [datasource,txmanager,org.springframework.aop.config.internalautoproxycreator,org.springframework.transaction.annotation.annotationtransactionattributesource#0,org.springframework.transaction.interceptor.transactioninterceptor#0,org.springframework.transaction.config.internaltransactionadvisor,personservice]; root of factory hierarchy
++++++++得到所有person
2 tomcat  12 女
3 hongten  21 男
4 liufang  21 女
5 divide  20 男
6 jone  20 女
7 苏东坡  21 男
******************
2 tomcat 12 女

当然我们可以用配置文件来存放我们的数据源信息：

/spring_1100_spring+jdbc/src/jdbc.properties

driverclassname=org.gjt.mm.mysql.driver
url=jdbc\:mysql\://localhost\:3307/spring?useunicode\=true&characterencoding\=utf-8
username=root
password=root
initialsize=1
maxactive=300
maxidle=2
minidle=1

相应要修改：

/spring_1100_spring+jdbc/src/bean.xml

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:context="http://www.springframework.org/schema/context"
  xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
  xsi:schemalocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

  <!-- 读取jdbc.properties配置文件 -->
  <context:property-placeholder location="classpath:jdbc.properties" />
  <!--配置数据源 -->
  <bean id="datasource" class="org.apache.commons.dbcp.basicdatasource"
    destroy-method="close">
    <property name="driverclassname" value="${driverclassname}" />
    <property name="url" value="${url}" />
    <property name="username" value="${username}" />
    <property name="password" value="${password}" />
    <!-- 连接池启动时的初始值 -->
    <property name="initialsize" value="${initialsize}" />
    <!-- 连接池的最大值 -->
    <property name="maxactive" value="${maxactive}" />
    <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxidle为止 -->
    <property name="maxidle" value="${maxidle}" />
    <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->
    <property name="minidle" value="${minidle}" />
  </bean>
  <!--
    采用注解方式来配置事务。针对数据源的事务管理器
    ，把我们定义的数据源注入到datasourcetransactionmanager类的属性datasource中
  -->
  <bean id="txmanager"
    class="org.springframework.jdbc.datasource.datasourcetransactionmanager">
    <property name="datasource" ref="datasource" />
  </bean>
  <!--
    引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx
    2.http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
    采用@transaction注解方式使用事务管理器
  -->
  <tx:annotation-driven transaction-manager="txmanager" />

  <!-- 配置业务bean：personservicebean -->
  <bean id="personservice" class="com.b510.service.impl.personservicebean">
    <!-- 向属性datasource注入数据源 -->
    <property name="datasource" ref="datasource"></property>
  </bean>
</beans>

运行结果是相同的：

2012-3-10 0:23:59 org.springframework.context.support.abstractapplicationcontext preparerefresh
信息: refreshing org.springframework.context.support.classpathxmlapplicationcontext@c1b531: display name [org.springframework.context.support.classpathxmlapplicationcontext@c1b531]; startup date [sat mar 10 00:23:59 cst 2012]; root of context hierarchy
2012-3-10 0:23:59 org.springframework.beans.factory.xml.xmlbeandefinitionreader loadbeandefinitions
信息: loading xml bean definitions from class path resource [bean.xml]
2012-3-10 0:23:59 org.springframework.context.support.abstractapplicationcontext obtainfreshbeanfactory
信息: bean factory for application context [org.springframework.context.support.classpathxmlapplicationcontext@c1b531]: org.springframework.beans.factory.support.defaultlistablebeanfactory@1aa57fb
2012-3-10 0:23:59 org.springframework.core.io.support.propertiesloadersupport loadproperties
信息: loading properties file from class path resource [jdbc.properties]
2012-3-10 0:23:59 org.springframework.beans.factory.support.defaultlistablebeanfactory preinstantiatesingletons
信息: pre-instantiating singletons in org.springframework.beans.factory.support.defaultlistablebeanfactory@1aa57fb: defining beans [org.springframework.beans.factory.config.propertyplaceholderconfigurer#0,datasource,txmanager,org.springframework.aop.config.internalautoproxycreator,org.springframework.transaction.annotation.annotationtransactionattributesource#0,org.springframework.transaction.interceptor.transactioninterceptor#0,org.springframework.transaction.config.internaltransactionadvisor,personservice]; root of factory hierarchy
++++++++得到所有person
2 tomcat  12 女
3 hongten  21 男
4 liufang  21 女
5 divide  20 男
6 jone  20 女
7 苏东坡  21 男
8 苏东坡  21 男
******************
2 tomcat 12 女

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。