jodd 操作jdbc实例

package com.jiepu.test;


import java.util.ArrayList;
import java.util.List;
import jodd.db.DbManager;
import jodd.db.DbQuery;
import jodd.db.DbSession;
import jodd.db.ThreadDbSessionHolder;
import jodd.db.jtx.DbJtxTransactionManager;
import jodd.db.oom.DbOomManager;
import jodd.db.oom.dao.Boy;
import jodd.db.oom.dao.BoyCriteria;
import jodd.db.oom.dao.GenericDao;
import jodd.db.oom.dao.Girl;
import jodd.db.pool.CoreConnectionPool;
import jodd.db.querymap.DbPropsQueryMap;

public class TestJoddDB {

    protected DbJtxTransactionManager dbtxm;
    protected CoreConnectionPool cp;

    public DbJtxTransactionManager getDbtxm() {
        return dbtxm;
    }

    public void setDbtxm(DbJtxTransactionManager dbtxm) {
        this.dbtxm = dbtxm;
    }

    public CoreConnectionPool getCp() {
        return cp;
    }

    public void setCp(CoreConnectionPool cp) {
        this.cp = cp;
    }

    public static void main(String[] args) throws Exception {

        TestJoddDB db = new TestJoddDB();
        //db.setUp("org.hsqldb.jdbcDriver","jdbc:hsqldb:mem:test","sa","");//ok
       
        db.setUp("org.sqlite.JDBC","jdbc:sqlite:test.db","","");//not ok sqlite3不支持 dao.update(girl);
        //db.setUp("com.mysql.jdbc.Driver","jdbc:mysql://127.0.0.1:3306/jodd?characterEncoding=UTF-8","root","mysql0774mysql");//ok


        
        DbSession session = new DbSession(db.getCp());

        ThreadDbSessionHolder.set(session);

        GenericDao dao = new GenericDao();

        // save
        Girl girl = new Girl();
        girl.setName("Emma");
        girl.setSpeciality("piano");
        girl.setId(Long.valueOf(1));

        dao.save(girl);
        System.out.println(girl.getId().longValue());

        Boy boy1 = new Boy();
        boy1.setId(1);
        boy1.setName("Oleg");
        boy1.setGirlId(1);

        Boy boy2 = new Boy();
        boy2.setId(2);
        boy2.setName("Marco");
        boy2.setGirlId(1);

        ArrayList<Boy> boys = new ArrayList<Boy>();
        boys.add(boy1);
        boys.add(boy2);

        dao.saveAll(boys);

        // find
        Boy dbBoy = dao.findById(Boy.class, 1);
        System.out.println(dbBoy.getId());
        System.out.println(dbBoy.getName());
        System.out.println(dbBoy.getGirlId());

        // update
        girl.setSpeciality("Guitar");
        dao.update(girl);
        

        long count = dao.count(Girl.class);
        System.out.println(count);

        Girl dbGirl = dao.findById(Girl.class, 1);
        System.out.println(dbGirl.getSpeciality());
        System.out.println(dbGirl.getName());

        // update property
        dao.updateProperty(girl, "speciality", "math");

        dbGirl = dao.findById(Girl.class, 1);
        System.out.println(dbGirl.getSpeciality());
        System.out.println(girl.getSpeciality());
        System.out.println(dbGirl.getName());

        // add one more girl
        Girl girl2 = new Girl();
        girl2.setName("Lina");
        girl2.setSpeciality("crazy");
        girl2.setId(Long.valueOf(2));

        dao.save(girl2);
        count = dao.count(Girl.class);
        System.out.println(count);

        Girl emma = dao.findOneByProperty(Girl.class, "name", "Emma");
        System.out.println(emma);
        System.out.println(emma.getId().longValue());

        Girl none = dao.findOneByProperty(Girl.class, "name", "Www");
        System.out.println(none);

        // find by matching
        Girl match = new Girl();
        match.setName("Emma");
        dbGirl = dao.findOne(match);

        System.out.println(dbGirl.getId());
        System.out.println(dbGirl.getName());
        System.out.println(dbGirl.getSpeciality());

        Boy boyMatch = new Boy();
        boyMatch.setGirlId(1);

        List<Boy> dbBoys = dao.find(boyMatch);
        System.out.println(dbBoys.size());

        // find by separate matching class
        boyMatch = new Boy();
        boyMatch.setName("Oleg");
        dbBoys = dao.find(Boy.class, boyMatch);
        System.out.println(dbBoys.size());

        // correct way
        BoyCriteria boyCriteria = new BoyCriteria();
        boyCriteria.setName("Oleg");

        dbBoys = dao.find(Boy.class, boyCriteria);
        System.out.println(dbBoys.size());

        // list all
        dbBoys = dao.listAll(Boy.class);
        System.out.println(dbBoys.size());

        // related
        dbBoys = dao.findRelated(Boy.class, emma);
        System.out.println(dbBoys.size());

        // delete
        dao.deleteById(Boy.class, 1);
        dao.deleteById(boy2);

        count = dao.count(Boy.class);
        System.out.println(count);

        // delete all
        List<Girl> girls = dao.listAll(Girl.class);
        dao.deleteAllById(girls);

        count = dao.count(Girl.class);
        System.out.println(count);

        dao.save(girl);
        dao.saveAll(boys);
        
        session.closeSession();
        ThreadDbSessionHolder.remove();

        db.tearDown();
        System.out.println("finish");

    }

    public void setUp(String driverClass,String url,String username,String password) throws Exception {
        DbManager.getInstance().setQueryMap(new DbPropsQueryMap());

        cp = new CoreConnectionPool();
        cp.setDriver(driverClass);
        cp.setUrl(url);

        cp.setUser(username);
        cp.setPassword(password);
        cp.init();
        dbtxm = new DbJtxTransactionManager(cp);

        // initial data
        DbSession session = new DbSession(cp);

        createTables(session);

        session.closeSession();

        DbOomManager.resetAll();
        DbOomManager dbOom = DbOomManager.getInstance();
        dbOom.registerEntity(Girl.class);
        dbOom.registerEntity(Boy.class);

    }

    protected void createTables(DbSession session) {
        executeUpdate(session, "drop table  if exists BOY");
        executeUpdate(session, "drop table  if exists GIRL");

        String sql = "create table GIRL ("
                + "ID			integer		not null,"
                + "NAME		varchar(20)	not null,"
                + "SPECIALITY	varchar(20)	null,"
                + "primary key (ID)"
                + ')';

        executeUpdate(session, sql);

        sql = "create table BOY ("
                + "ID			integer	not null,"
                + "GIRL_ID		integer	null,"
                + "NAME	varchar(20)	null,"
                + "primary key (ID),"
                + "FOREIGN KEY (GIRL_ID) REFERENCES GIRL (ID)"
                + ')';

        executeUpdate(session, sql);
    }

    public void tearDown() throws Exception {
        dbtxm.close();
        cp.close();
        dbtxm = null;
    }

    // ---------------------------------------------------------------- helpers
    protected int executeUpdate(DbSession session, String s) {
        return new DbQuery(session, s).autoClose().executeUpdate();
    }

    protected void executeUpdate(String sql) {
        new DbQuery(sql).autoClose().executeUpdate();
    }

    protected long executeCount(DbSession session, String s) {
        return new DbQuery(session, s).autoClose().executeCount();
    }

}

</pre><pre name="code" class="java">// Copyright (c) 2003-present, Jodd Team (http://jodd.org)
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions are met:
//
// 1. Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
//
// 2. Redistributions in binary form must reproduce the above copyright
// notice, this list of conditions and the following disclaimer in the
// documentation and/or other materials provided with the distribution.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
// AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
// ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
// LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
// CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
// SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
// INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
// CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
// ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.

package jodd.db.oom.dao;

import jodd.db.oom.meta.DbColumn;
import jodd.db.oom.meta.DbId;
import jodd.db.oom.meta.DbTable;

@DbTable
public class Girl {

	@DbId
	protected Long id;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	@DbColumn
	protected String name;
	@DbColumn
	protected String speciality;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSpeciality() {
		return speciality;
	}

	public void setSpeciality(String speciality) {
		this.speciality = speciality;
	}
}

// Copyright (c) 2003-present, Jodd Team (http://jodd.org)
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions are met:
//
// 1. Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
//
// 2. Redistributions in binary form must reproduce the above copyright
// notice, this list of conditions and the following disclaimer in the
// documentation and/or other materials provided with the distribution.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
// AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
// ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
// LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
// CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
// SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
// INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
// CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
// ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.

package jodd.db.oom.dao;

import jodd.db.oom.meta.DbColumn;
import jodd.db.oom.meta.DbId;
import jodd.db.oom.meta.DbTable;

@DbTable
public class Boy {

	@DbId
	long id;

	@DbColumn
	long girlId;

	@DbColumn
	String name;

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public long getGirlId() {
		return girlId;
	}

	public void setGirlId(long girlId) {
		this.girlId = girlId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

// Copyright (c) 2003-present, Jodd Team (http://jodd.org)
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions are met:
//
// 1. Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
//
// 2. Redistributions in binary form must reproduce the above copyright
// notice, this list of conditions and the following disclaimer in the
// documentation and/or other materials provided with the distribution.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
// AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
// ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
// LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
// CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
// SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
// INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
// CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
// ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.

package jodd.db.oom.dao;

import jodd.db.oom.meta.DbColumn;

public class BoyCriteria {

	@DbColumn
	Long girlId;

	@DbColumn
	String name;

	public Long getGirlId() {
		return girlId;
	}

	public void setGirlId(Long girlId) {
		this.girlId = girlId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}